Much of science and economics is about the rate of change, often with time. This rate of change is the slope of a graph, spreadsheets make this easy to find.
Children are taught the equation of a line at primary school, which includes the slope. It is much less common for them to be taught how to calculate the slope of a line from some data, which is strange as they are taught how to use spreadsheets and spreadsheets make this easy.
This post will show you how. I reckon children around 9 or 10 could reproduce the spreadsheets in this post, perhaps with a little help. My 16 year old daughter whizzed through the post creating all the spreadsheets in about 15 minutes.
If 1 ice cream costs £3 then
2 ice creams cost £6 and
3 ice creams cost £9
The cost is 3 times the number of ice creams, which could be written as the equation
y = 3 x (which means 3 × x – the × is usually not written in equations)
where y is the cost of the ice creams and x is the number of ice creams.
We could draw up a table of number of ice creams and how much they cost.
Spreadsheets are ideal for this, for this post I’ve used the free Open Office Calc
Figure 1
The 3.0 in cell B16 is the slope of the graph, as calculated by the slope function.
In the next cell, C16, I’ve entered the formula as text. I find it is useful to do this so when I look at the spreadsheet a few months later I’ve got a note to help me remember what is going on.
The spreadsheet will even plot a graph of the line which shows how the cost varies with number of ice creams. And it will even write the equation of the line as in the graph below
y = 3 x
The slope of this line is 3, the number which multiplies x.
The spreadsheet can calculate the slope of a line from a two columns of numbers using a spreadsheet function called slope (as in the example above).
Figure 2
But we knew the number that multiplied x was 3 (each ice cream cost £3).
This is just a graphical way at looking at what was covered in this post
11+ And KS2 Maths – A Common Type Of Question Has Divide And Multiply For Scaling
What about when we have a graph but we don’t know the slope of the line?
Here is a table of values, like the one above, except a small random amount has been added to or taken away from each y value (the cost of the ice creams), the random amount is in column C.
Figure 3
The slope is not exactly three and the line through the data points is not exactly straight, thick dark blue line. There is also a thin light blue straight line (mostly covered by the dark blue line) which is the line of the equation.
Figure 4
This line is called the trend line, it is the straight line which the spreadsheet has calculated is the best straight line ‘through’ the points given no straight line actually goes through all the points.
A real example.
Permanent Service for Mean Sea Level (PSMSL) hold measurements of sea levels from around the world. Here is a graph of sea levels at Newlyn, Cornwall from 1916 to 2014, sea levels are measured in mille metres(mm).
Figure 5
The slope of the trend line is 1.82, which tells us the average rate of sea level rise at Newlyn is 1.82 mm/year.
There are some periods (e.g. 1970 to 2000) when sea levels have been mainly below the trend line, and others (e.g. 1950 to 1970) when sea levels have been largely above.
There is no sign of a recent acceleration in rate of sea level rise. It is claimed by those who support Climate Change/Global Warming the rate of sea level rise is now above 3mm/year, there is no sign of this in the graph.
Indeed you could even ask is Global Warming happening at all given the lack of acceleration in the rate of sea level rise, given this is a predicted consequence of Global Warming.
So here is an example of how simple arithmetic, the use of freely available data and software tools allows you and your children to check if what you are being told by politicians and the media is true. In this case it would seem not.
Now I’m going to show you,how you and your children can create the spreadsheet results shown above. For this first chart I’m going to go through the steps in great detail. There may seem a lot of things to do but if anyone gets involved in presenting numeric data (at school, college or at work) they will do this sort of thing so often it becomes second nature.
But if you’d rather not do that you can download slopeOfLine.xls
To create the 2 columns of number of ice creams you could just type the numbers in, which wouldn’t be to bad with just 20 numbers. But there are 2 short cuts.
Enter 1 in cell A4, then drag the cursor over cells A4 to A13
From menus select: Edit/Fill/Series
This will open the Fill Series dialog box
click ok and the numbers 2 to 10 will be entered in cells A5 to A13
Click cell B4 and enter the formula =a4*3
I’ve entered the formula as text in cell C4 as a reminder, just put a ” first (“=a4*3)
Once you hit return the spreadsheet will display result of formula
Click on cell B4 again and copy (ctrl + c)
drag the cursor over cells B4 to B13
and paste (ctrl + v)
the spread sheet will enter formula in each cell after adjusting the references, so in cell B5 the formula becomes =A5*3.
Now we can draw graph with trendline
drag the cursor over cells A4 to B13
From menus select Insert/Chart
In the Chart dialogue select Chart Type/XY Scatter/Lines Only
then click Finish
The chart will be displayed with a blue border. When the chart has a blue border this means the chart has been selected and the spreadsheet menus are changed to allow you to alter the properties to be edited. If the chart is not selected (i.e. it doesn’t have blue border, just double click on it and the blue border will come back).
(Note there will be some text displayed to right of graph saying something like
Column B, you can click on this (to select) then press delete to get rid of it).
When the chart is selected you can use the Insert/Trend Lines to insert a trendline.
From the Trend Lines dialog box select Linear and Show Equation then click ok
and we’re done
Here I’ve entered the slope function in cell B16 and displayed what I’ve entered as text in C16.
The second graph is very similar to the first so I will briefly describe the steps but point out the differences.
Enter 1 to 10 in column A as before. I used the same spreadsheet and cells A27 to A36
In B27 enter the forumla =A27*3 + C27
In C27 enter the formula = rand()-0.5
There are a lot of numbers after the decimal point.
Select menu Format/Cells
Select Numbers tab of the Format Cells dialog, then the entry which only displays 2 numbers after the decimal point, then click OK.
Then drag cursor over cells B27 and C27, to select them
Copy (ctrl + c)
Drag cursor over cells B28 to B36
Paste (ctrl + v)
The spreadsheet will recalculate different random numbers each time it recalculates the sheet. In Open Office Calc, and in most spreadsheets, pressing F9 will cause the sheet to be recalculated.
Now you can select cells A27 to B26 and insert the chart and display the trendline as before.
Despite having set the format of numbers in cells B27 to C36 to only display to numbers after the decimal point, this will not necessarily be the case with the trendline equation.
If the chart isn’t selected (have blue boudary) double click on it.
Then double click on trendline equation to select it.
Right click on trendline equation
Select Format Trend Line Equation
Select Number tab
Select to only display 2 numbers after decimal point (as before)
and we’re done
For the next graph we need to get the annual sea level data for Newlyn from PSMSL.
If you’re like to get straight to the point click on the link below.
http://www.psmsl.org/data/obtaining/rlr.annual.data/202.rlrdata
Otherwise we’ll head over to
If you click on graphic next to the one labelled ALL (i’ve written click here on the image above).
You’ll be presented with a long table which seems to start with Scandinavian countries, then Baltic, Germany, Holland and Britain. British stations seem to start from north east Scotland and proceed clockwise around the coast. Newlyn is in Cornwall and numbered 202 by PSMSL.
If you click on the 202 you will be presented with a page specific to Newlyn
And if you scroll down a bit there is a link to download annual sea level data
When you click on this link you will be presented with a page like this.
Click on the page
select all (ctrl a)
copy (ctrl c)
Head back to a spreadsheet,
click on a cell (e.g. A1)
paste the data for Newlyn (ctrl v)
And you get the data but it’s all in one column.
Select all the rows with data (for me this was A1:A99)
From menus select Data/Text to Columns
Ensure semicolon is ticked in Text to Columns Dialog
you even get a preview of your data.
Click OK
And we’re done
Almost
When PSMSL is missing data for a year they enter -99999.
At the time of writing this only happens in 2007 and 2010
You can either delete the rows or simpler, click in each cell in turn and press backspace key. This will cause the spreadsheet to think these cells have no data, which means it will ignore these rows if they are included when using slope function or drawing a graph. You could always leave -99999 in draw graph, use slope function, the get rid of -99999s and see the difference it makes.
Now you can select the cells (A1:B99)
Insert Chart
Add Trendline
And do formatting as before and you will end up with something like
There is a mass of free data available on the internet (all the other stations on PSMSL for a start) so you can grab the data and fit trend lines.
How about this from the wonderful woodfortrees.org
http://woodfortrees.org/data/hadcrut4gl
Though you will have to remove the extra lines before and after the data.
When you do a plot using woodfortrees the url is something like this
http://woodfortrees.org/plot/hadcrut4gl
Edit the url changing plot for data
and it gives you the data the plot is based on.
Have a go and let me know how you get on.
