MAS116/MAS117 PRESENTATION LAB 10

This week we will look using spreadsheets to investigate mathematical problems, including tips for presenting the findings well.

Excel vs Google Sheets vs Other

The basics: a spreadsheet consists of cells, each of which is has an address, such as A2 (first-column, second row). Inside each cell can either go a value – text or a number – or a formula. Formulas must start with an ‘=’-sign.

You probably know Microsoft’s Excel software, which is widely used. If you are working on one of the University’s PCs, then I recommend using that for today’s lab. If not, and you don’t have a version of Excel to use, then Google Sheets (accessed through a web-browser) works fine with all except the last part of this week’s sheet. In general, I use Google sheets as it works well with my university account and it is easy to share sheets. If neither of those are an option, I expect most other spreadsheet software will work OK for most of the sheet, e.g. Numbers (on a Mac) or Open Office.

1. Projectiles

Go to the course website and save the file called projectiles.xlsx found in the Week 11 lab materials. Open this in Excel or upload it to Google Sheets.

By looking at the contents of the cells, try to work out what it does. The presentation is poor, so this won’t be easy. We will improve that. The data actually represents the motion of a projectile, with the first column being time, and the next two columns being the $x$ and $y$ coordinates of the projectile.

(1): Insert a row at the top of the spreadsheet. The best way to do this is to highlight row 1, right-click and choose insert.
(2): Label the three columns of data.
(3): Make your column labels bold and right-aligned.
(4): Highlight the time data. In Excel, right-click and select format cells. Select the Number format, choose 1 decimal place and click OK. In Google Sheets, go to the Format menu and choose Number, then the Number format (or use the Custom number format to specify 1 decimal place).
(5): Format the $x$ and $y$ columns to have 2 decimal places.

You’ve probably noticed that some cell references have $-signs in, e.g. $A$1. These are what’s known as absolute references. Absolute references don’t change when using the fill handle (see later).

Hopefully the data is looking better now. You will notice that, on the right, there are parameters that are referenced in the formulas calculating the $x$ and $y$ values.

(6): Insert a column to the left of Column E by highlighting the column, right-clicking and choosing insert.
(7): Label the four parameters in the column you created. The first is the gravitational constant $g$ , the second is the angle of launch, $𝜃$ ,then there is the initial speed, $v_{0}$ , and finally the initial $y$ -value, $y_{0}$ .
(8): Center the labels you just created. Format the values to be displayed to 2 decimal places.
(9): Create titles of ‘Parameter’ and ‘Value’ for the two columns.
(10): Add a column called ‘Description’ between your two columns, and give a short description of each parameter (e.g. ‘Initial speed’).
(11): I’ve decided that the ‘Description’ column works better after the ‘Value’ column, so highlight the column, then cut and paste it.

Let’s draw a graph of the flight of the projectile. Highlight the data in the table corresponding to the $x$ and $y$ coordinates, including the titles, then

In Excel, go to Insert, Scatter, Scatter with Smooth Lines from the menu at the top;
In Google Sheets, go to Insert, Chart.

Drag this graph to a suitable place on the page. Try changing the parameters (other than $g$ !) to vary the projectile’s path.

To make sure the projectile can’t disappear below ground level, insert a new column to your main table called floor(y). In the top cell, use the IF function in Excel to make that cell display the $y$ value if it is positive and $0$ otherwise. Excel should help you as you start to type ‘=IF(...’, and in Google Sheets a small ‘?’ should appear with help on how to use the IF function. Use the fill handle to complete the rest of the column.

If you don’t know how to use the fill handle then search YouTube for ‘How to Use the Fill Handle’ and watch a short video.

Delete the graph, then re-plot the $x$ values against the adjusted $y$ values. Alternatively, edit the data range for the graph if you can find out how.

Finally, the spreadsheet needs a title. Insert two blank rows at the top, and in cell A1 type ‘Projectile motion’. Make the title 18pt and bold.

2. Euclid’s algorithm

You have covered Euclid’s algorithm for finding the highest common factor of a pair of integers earlier this semester. Let’s look at implementing it in a spreadsheet.

Start a new spreadsheet and save it. As a test run, we’ll use the numbers 1001 and 132.

(1): Enter ‘a’ in cell $A 1$ and ‘1001’ in cell $B 1$ .
(2): Enter ‘b’ in cell $A 2$ and ‘132’ in cell $B 2$ .
(3): In $D 1$ enter ‘Remainders’. In $D 2$ , put ‘=B1’. In $D 3$ put ‘=B2’. (This column keeps track of the successive remainders in Euclid’s algorithm.)
(4): To keep track of the quotients, give column $E$ the title ‘Quotients’. Leaving $E 2$ blank, make $E 3$ calculate the integer part of $D 2 ∕ D 3$ . (The function INT will be useful here.)
(5): Use the quotient you just calculated to enter the next remainder in cell $D 4$ .
(6): Continue in this way to calculate further quotients and remainders. You should use the fill handle to make this automatic, perhaps dragging down as far as row 20 (which should more than far enough!).
(7): Eventually, you’ll find a remainder of 0 appearing and some error messages. What’s the conclusion at this stage? Why do you see errors appearing?

Essentially, the algorithm is now complete. Change the numbers $a$ and $b$ to see the workings change. As usual, a bit of effort with the presentation here can make the spreadsheet much more user-friendly.

Start a column to the right of the ‘Quotients’ column called ‘Description’. We’ll use the CONCATENATE function to give a description of what the calculation has shown at each stage.

(8): In cell F3, use CONCATENATE to display the statement ‘D2=E3*D3+D4’; that is, giving the output ‘ $1001 = 7 * 132 + 77$ ’.
(Hint: CONCATENATE joins together strings, like the + operator in Python. Here, you need to join the value in D2, an ‘=’-sign, the value in D3, a ‘ $*$ ’-sign, etc.)
(9): Use the fill handle to complete the rest of the column.

Now to get rid of the unsightly error messages. The function IFERROR is good for this, which gives an alternative output for a formula if the formula returns an error.

(10): Wrap your formula in D4 in an IFERROR command to give a blank string instead of an error message. That is, change ‘=formula’ to ‘=IFERROR(formula,"")’.
(11): Use the fill handle to drag this down. You should see the error messages disappear.
(12): Use the same techniques on the other columns containing error messages.

Note: Unfortunately, Google Sheets and Excel don’t behave in quite the same way in terms of error messages, and you may find spurious numbers appearing in rows after the calculation is complete. If you want to tidy things up, try using the =IF(... and =OR(... functions to check whether the remainder at the previous stage is either zero or blank to blank the output.1

Make sure everything is working at this stage by altering $a$ and $b$ and watching the content of your spreadsheet change.

A nice touch would be to display the sentence ‘The highest common factor of $*$ and $*$ is $*$ ’ at the top of the page. Try to find a valid approach. Have a look at my lab attempt on the course webpage if you get stuck.

All that’s left to do is to add two rows at the top, and put in the title ‘Euclid’s algorithm’ in 18pt bold.

3. Using solver

(Excel only!) As a final task, let’s look at the Solver feature of Excel, which can maximise or minimise expressions subject to constraints.

On the module webpage, download and save the spreadsheet solver.xlsx. The spreadsheet shows data for unknown function $f$ . Our task is to try to to fit an appropriate curve.

Notice that the plot of the data shows something that looks like a damped oscillation (that is, a sine curve that becomes increasingly less energetic). An appropriate function to try to fit here would be something of the form $g (x) = e^{- Kx} (A + B \sin (Cx + D))$ , since data is off-axis damps towards zero. Let’s see if this hunch is correct.

(1): In column C, put the title $g (x)$ . Enter a formula in C6 which calculates the function $g$ as given above, using the EXP function and the parameters $A$ – $K$ that appear in the given table.
(2): Use the fill handle to complete the column. Do you notice a problem? Look carefully at the formula in the filled cells.
(3): Unless you were already wise to the situation, you will have found that the references to the parameters changed as you filled the column. To correct this, you will need to use absolute references for the parameters in your original formula (e.g. $G$6). Amend the formula, and fill using the fill handle.
A quick way to change to an absolute reference when typing a formula is to press F4.
(4): In column D, work out ${(f (x) - g (x))}^{2}$ . This will give a positive number which measures the difference between the curves.
(5): Use the SUM function to give the total for column D, placing it at the bottom of the column.

Here’s the clever bit! We’re going to get Excel to work out the best possible values for the parameters by minimising the sum of the squared differences you just calculated. To do that, we use Solver.

To enable Solver, go to File, then Options and select Add-ins. At the bottom, find the bit that says Manage: Excel Add-ins and click Go. Select Solver and click OK.

If you can’t get Solver to install properly, then I’m afraid you will have to leave this sheet here! Otherwise, continue...

Now, look in the Data menu at the top. You should see Solver on the right-hand side. Click to start it.

We want to minimize the cell containing the sum of the squares by changing the parameter values. Thus,

(6): set the ‘objective’ cell to the one containing the sum of the squares;
(7): select Min;
(8): select the cells containing the parameter values in the ‘By Changing Variable Cells’ box;
(9): click Solve.

All being well, Solver will work out the best possible parameter values. Check the graph to see if it’s been successful.

1The formulas may end up looking something like =IF(OR(E11=0,E11=""),"",IFERROR(D10-E11*D11,""))