Using spreadsheets

Printer-friendly versionPrinter-friendly version Share this

Want a quick way to do calculations, simulate data and draw graphs? Here are some quck tips on using spreadsheets to do this!

Big Ideas: 
  • Spreadsheets are very helpful in analyzing experimental data, both for repeated calculations and for graphing results.  Using a model, a simplified and idealized physical and mathematical construct, helps us understand and make useful predictions about an experimental system.

Tools Needed:

  • OpenOffice Calc 3.2 (or some other spreadsheet software, such as Microsoft Excel or NeoOffice Calc)
  • (for Video 4) Extension: Solver for Nonlinear Programming (or another solver capable of optimizing nonlinear functions, such as the Excel Solver Add-in)

Physics tells us that an object in free-fall should accelerate at a constant rate. The equation below shows how the final velocity, $ v_f $, and the initial velocity, $ v_0 $, change with time.

$ v_f = v_0 + at $

On the surface of the Earth, this acceleration is called the gravitational constant, or g, and approximately equal to 9.8m/s2. For an object in free-fall near the surface of the Earth, the above equation becomes:

$ v_f = v_0 - gt $

This is called a model. It describes how objects in free-fall are expected to behave. We are first going to use our spreadsheet to generate the values for vf over time predicted by our model. Throughout this activity, we will assume that the starting velocity of our object, vo, is 0m/s. We will then graph this velocity, create simulated values and do a chi squared test to assess uncertainties.

There are 2 parts to this tutorial -

Video 1: Introduction to Spreadsheets

This video takes you through a step-by-step process on how to use spreadsheets when doing calculations, and constructing graphs. The example used in this video calculates the velocity of a body during free fall over a set period of time. Using spreadsheet functions, we enter the time over which the body falls, breaking it down into unit time steps. We also use the formula feature of spreadsheets to calculate the velocity and finally, we construct a graph of the velocity vs time. These techniques are extremely usefull for doing repeatable calculations, and in the analysis of experimental data.Key techniques learnt here are entering formulas into cells, using the drag-down function, absolute cell references and graphing techniques.

Using spreadsheet video 1.mp4 (right-click and choose "Save Link As..." to download to your computer)
Finished spreadsheet for video 1.xls

Video 2: Creating simulated error in data

This video explains how to create data which has random measurement error. Using spreadsheet functions, we simulate the kind of error you would expect if you took measurements in the real world. The video is made for beginning users of the Open Office Calc spreadsheet program and reviews how to enter formulae into cells, make absolute or relative cell references, and how to copy formulae into other cells. It also introduces the NORMSINV() and RAND() spreasheet functions. Scientists use these techniques in order to make models of experiments before actually conducting them in order to generate hypotheses. Students can learn that data measured in the real world, even though it follows an underlying theory, is never "perfect". The techniques in this video can even be used to try and get away with scientific malpractice, such as that conducted by Hendrik Shön

Using Spreadsheets Video 2.mp4 (right-click and choose "Save Link As..." to download to your computer)
Finished spreadsheet for video 2.xls

Video 3: make a best fit line visually

This tutorial explains how to create a best fit line visually, using Open Office Calc. It gives instructions for plotting multiple sets of data on the same graph, formatting how the data is plotted (as a line instead of as points), and shows how to put error bars on the graph.

Using_Spreadsheets_Video_3.mp4 (right-click and choose "Save Link As..." to download to your computer)
Finished spreadsheet for_Video_3.xls

Video 4: use Chi-Square analysis to fit any function

This video explains the chi-square formula, and then shows how to use it to fit a set of data using Open Office Calc.  The same techniques can be used with Microsoft Excel.  After this video you will also know how to use the Open Office Solver for Non-linear Programming (which has an intimidating name, but is actually easy to use!).

Using_Spreadsheets_Video_4.mp4 (right-click and choose "Save Link As..." to download to your computer)

Video 4.2: use Chi-Square to fit a periodic function

This video shows the use of chi-square analysis to fit a periodic function.

Using_Spreadsheets_Video_4.2.mp4 (right-click and choose "Save Link As..." to download to your computer)



Post new comment

Please note that these comments are moderated and reviewed before publishing.

The content of this field is kept private and will not be shown publicly.
By submitting this form, you accept the Mollom privacy policy.

a place of mind, The University of British Columbia

C21: Physics Teaching for the 21st Century
UBC Department of Physics & Astronomy
6224 Agricultural Road
Vancouver, BC V6T 1Z1
Tel 604.822.3675
Fax 604.822.5324

Emergency Procedures | Accessibility | Contact UBC | © Copyright The University of British Columbia