In this lab you will learn how to analyse your data using some of the functions and features offered by OpenOffice.org. These features have been discussed in detail during the lectures, and in this lab you need to apply that knowledge to obtain relevant information from various data sets.
Each lab is worth 3 marks: 1 mark from the online assessment and 2 marks from your tutor's assessment of your lab work.
Please download the lab spreadsheet document lab03.ods for the lab exercises.
Imagine that your employer has asked for some analysis of global temperature trends. The sheet Ex1_GlobalTemps contains a column of temperature values for the years 1850 to date. They are estimates of the global mean temperature over the Earth's surface averaged over each month of the year, based on integrating measurements from thousands of locations across the globe. The data is a summary of what's known as the HadCRUT4 data set. The numbers are expressed as differences in degrees Celsius from the 1961-1990 mean, and are called "anomalies".
Construct a scatter chart showing the data: select the X and Y data as a single 2-column range, including labels at the top, then choose a suitable chart type (lines, no markers). Note the trend of temperature from 1850 to 2014. Is it a rising or falling trend?
You have learned in the lecture that data can be used to do prediction. In this exercise, you will predict the future temperature by using two different ranges of past temperature data. You will first use a shorter data range, from 2003 on onwards, to do the prediction; after that, you will use the data from 1961 onwards to do another prediction. You will see later that the choice of ranges can have a significant effect on the prediction.
When the chart is in edit mode you can select any element easily with the drop-down list where the name box normally is. Select the Anomaly 1961 to date series and add a linear trend line and its equation. You can adjust its colour by right-clicking or using the drop-down and adjacent Format Selection button.
The x-coefficient in the equation is what the trend model claims is the mean annual increase in global temperature over the period. Type that value into the yellow box F22.
Turn off the auto-scaling for the X-axis and make it range from
1870 to 2070 to see the predicted increases over or beyond your
working lifetime. Adjust the Y axis so you can see the top of the
trend. In what year does the line cross the 1 degree axis? (The
consensus among most climate scientists is that 1.5 to 2.0 degrees
above pre-industrial levels, or about 1.0 to 1.5 degrees above the
baseline in this data, is a tipping point where runaway warming is
likely to occur.)
Write your answer in the yellow cell (H22) on the worksheet.
Much of the debate, or misinformation, around climate change surrounds apparent trends over the last few years. Add a trend line for the Anomaly 2003 to date series. This is such a short time in historical terms that random variation is a factor and any interpretation becomes far more subjective. Nevertheless, apply a linear trend: this is a gold mine for climate skeptics! Soon everything will be back to normal, according to this flawed analysis. Enter the slope in the yellow cell F23.
Moral: Data is never really objective, as decisions about its selection and interpretation can profoundly influence the results of reasonable and objective analysis. Although there are many statistical games that we can play, it's also possible to do more analysis.
One method is to check which of the two predictions Anomaly 1961 to date or Anomaly 2003 to date is better. The two trendlines give predictions for years 2015, 2016 and so on. You may perhaps wait for the data from year 2015 and then see which of these two trendlines will give you a better prediction. Say, if the trendline based on Anomaly 1961 to date gives a prediction closer to the actual temperature anomaly in 2015, then we may have a bit more confidence in the prediction of Anomaly 1961 to date. You can do this again in 2016.
You may ask whether there is a real need to wait for 2015. Can we do this sooner? Yes, you can. You can use (1) the data from 2003-2013 to predict the temperature in 2014; (2) The data from 1961 to 2013 to predict the temperature in 2014. You can check which one will give you a better prediction.
The technique that we have used in the last paragraph to gauge how well the trendlines predict is studied under the topic of model validation in statistics.
Note: Your employer believes presentation is important, so they
insist that the chart is well presented, with a neat, clear title and
year scale. The three traces and two trendlines should be identified
using different line colour. For maxmimum marks, you must show you can
We can use a chart (plot) to search for a possible root(s) of a given equation, mainly so we know where to direct more detailed efforts.
In the worksheet named Ex2_Roots, create a table as shown
below for the values of x and f
(x), where f (x) =
x3 – 17x + 12. The value of x
should range from –5 to 5, with the increment of 0.4, or whatever is
in cell $C$8.
Plot a graph as shown below and estimate the three root values from this graph.
Using the Goal Seek tool and the pink and green boxes as a workspace, calculate each of the three root values. Copy each answer to the boxes provided, in increasing order, using copy/paste values rather than typing. The auto-assessment will confirm that you have used Goal Seek properly, and will check the largest root to more than the number of decimal places displayed.
St Louis Gateway Arch
Photographer: Daniel Schwen
"Catenary" (from the Latin for chain) is the name given to the shape of
a flexible rope or cable that's fixed at each end and sags under the
influence of gravity. An inverted catenary forms an elegant arch, as
At first glance it might look like a parabola, but actually it's described by the hyperbolic cosine function. Don't panic: all you need to know is that cosh (pronounced "cosh") and sinh (pronounced "shine" or sometimes "sinch") are just ways of expressing sums and differences of simple exponentials, although they have their own wonderful system of trigonometry.
The overall shape is governed by the parameter a. It's the pink variable cell on the Ex3_Catenary sheet, and everything else depends on it.
First plot f (x) to see the shape. We've filled in the x column from A20 down for 100 points, and the coloured cells in column D have defined names to make it much easier to enter formulas. Fill in the y = f (x) formula in B20 and fill down. Chart the result using a scatter plot, smooth lines, no markers.
When a is 50 or so the shape is very much like a parabola, but change it to 15 and the flattening of the lower part is evident.
Enter formulas for S and h in the cells provided. The cosh and sinh functions are defined in Calc.
Assume a real cable is going to be used to span a 120m gap, and its linear density w is 2.5kg/m (typical of a 25mm diameter multistrand steel cable). The cable has a total mass m, which exerts a downward force Fv that varies with distance from the centre, and it exerts a uniform horizontal force Fh . The relevant equations to calculate the tension T at the supports are
We can manipulate any parameter of the model without using any
algebra at all. Just ask Goal Seek to adjust the value of a
until a desired property such as cable length, sag or maximum
tension is achieved. There are 4 questions on the sheet that you can
answer with the help of Goal Seek, but try this first:
You've been asked to design a simple flexible footbridge, but to minimise vertical movement the client has specified a maximum of 8m sag over the 120m span. You normally use supports that can safely withstand 5kN. Will the supports be adequate?
Remark: suspension bridge cables usually support a roadway, which has much greater mass than the cables themselves. In this case the curve is indeed parabolic, not catenary.