Printer-friendly version

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, , and the initial velocity, , change with time.

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:

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 -

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.

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

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.

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!).

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.

Resources
Resources: