Analyzing and Graphing Lab Data with Open Office Calc

One of the most important uses of spreadsheets in business, academia and for fun is in the statistical analysis of data.  For many students on a shoestring budget, this means the use of the free to use OpenOffice Calc.  While this is a powerful spreadsheet tool that is as capable as Microsoft Excel, it does have some idiosyncrasies that can be frustrating to the novice user.

In this week’s newsletter, we will show how to analyze and graph some lab data that might be collected in a typical high school physics lab.  We will give an overview of several useful features built into OpenOffice Calc.

Entering the raw data:

As ever we start with our raw data.  While allowing a glider to slide down a nearly frictionless air-track, a student collects the following observations of its speed:

The first step is to create a new spreadsheet and enter our data into it. We rename Sheet 1 to ‘Raw Data’, and type in the data into columns A and B.  If you wish to skip this step, you can download a spreadsheet containing the unformatted raw data here here.

Formatting the Data

Initially, the data is hard to read.  The numbers show different number of digits  after the decimal point.  Nor are digits aligned properly.  We fix this by editing the formatting of the cells.

We want the time values to be listed to tenths of a second.  We want the velocity values to also be listed in tenths of a cm/sec.  We alter the size of the font to make the data more readable. We then adjust the column widths to be appropriate for the data.  Finally, we add borders around the data to make it more readable.

Inserting the Chart

We wish to produce an v vs t chart.  We highlight all the data, including the labels, and select Insert → Chart.  We use the Chart Wizard to define the chart we want:

1. ‘Chart Type’ dialog: we select ‘XY(scatter)’, specifying that we are interested in a scatter plot containing ‘Points Only’
2. ‘Data Range’ dialog: we select $’Raw Data’.$A$1:$B$12 as our data range. We specify that the data series are in columns and that the first row of each column is the label of that data series.
3. ‘Data Series’ dialog: we specify the data ranges that contain coordinates and labels for the data series we wish to plot.  fortunately, the wizard will do all the work for us, and we will not need to alter the settings on this screen.
   The wizard will detect that we only have two columns of data, and will assume, correctly, that the leftmost column, our time values, is the independent variable, which we will use as the x-coordinates in our plot.  It will assume that all the columns to the right of the first one are the data series providing the y-coordinates for the plot.  In this case, since we only have two columns, so the wizard  only generates one data series for our graph.
4. ‘Chart Elements’ dialog: we specify the title, subtitle, and axis labels of the chart.  We also specify that we want grid-lines for  both the x and y axes.

Once we have finished with the wizard, we have a chart, but it is not very presentable. Cut and paste the plot onto a new worksheet. Drag the edges of the plot to fill up the visible region of the worksheet. Rename the worksheet ‘Plot’.
We then double click on the chart to edit it.

We double click on chart elements to adjust their appearance. We alter the appearance of the data series, making the data points smaller. We alter the ranges of the axes to improve the appearance of the display.

We can see the results here. I encourage you to download and experiment with the enclosed spreadsheet.

Analyzing the data

From the plot, we see an apparent linear relationship between the velocity and time. In order to identify the parameters of the linear relationship, we perform a linear regression. You can observe the work we did by looking at the enclosed spreadsheet.

We create a new worksheet to hold the data, and duplicate our raw data by building a table of cells that are set equal to the corresponding cells in the raw data.

We may not want to include all the data in our analysis. After all, one or more of the data points may have been taken in error. So we will add a column that specifies whether or not the data will be used in the analysis. Since we will want to eventually add a trend-line to our plot, we skip column C and store “Y” values in column D.

We then create a conditional table, which has the original raw data if the ‘include’ value is set to “Y”, and leaves the cell blank if the value is set to anything else.

Finally, we perform our linear regression analysis. We make use of the OpenOffice functions SLOPE and INTERCEPT to find the parameters. We also use the function CORREL to find the correlation coefficient for the linear regression (R).

We use the slope and intercept to calculate the values that are predicted by the parameters. This is added as a data series to the chart.

To add the data series, we double click on the chart to edit it, and then right-click on the plot area, and select ‘Data Ranges’ and add the range as shown in the video.

We edit the range to add a line between the data points, and remove any data point markers.

From the experimental data, it is clear that the last observation was made at a point where the glider was no longer traveling freely. We therefore decide to discard it from the regression analysis.

We are then done. We have experimentally found the line of best fit through our data, and have a nice plot showing our data.  Thie final spreadsheet may be found here.