The point of this lecture is to show one way Excel can be used in teaching introductory dynamics: analyzing data generated by a simple in-class experiment.

 A detailed discussion of constructing the data table and charting it in Excel is given in the first part below.

Note: to actually do this experiment, you need a video camera and a VCR with frame by frame playback capability. If you just want to find out how to use Excel to analyze results of such an experiment, you can use the fake data supplied below.

We begin by considering the motion of a falling ball, observed using a video camera. Details here. The idea is to have a student drop a ball, well lit with a black cloth behind, and close to a vertical two-meter stick with ten-centimeter intervals alternatively black and white, to measure the distance fallen.

 We take a video of the falling ball, and observe the motion in a frame-by-frame playback. That way, we see exactly where the ball is at a succession of times one-thirtieth of a second apart. (You can check the time interval by taking a video of a clock with a sweep seconds hand.)

 The first point to notice, in doing the frame by frame playback, is that the ball does indeed pick up speed at a pretty steady rate—in other words, the distances fallen in succeeding thirtieths of a second can be seen to increase more or less linearly. Next, knowing the frame rate is thirty per second, graphs of distance fallen against time and speed against time can be constructed.

Open a new Excel workbook, write "time" (no quotes, of course) in cell B6, say, and "distance" in C6, we’re leaving space above to add notes, etc. In fact, we could write "Distance Fallen under Gravity" in cell A1. At time zero, the instant the fall begins, the distance is also zero, by definition, so put "0" in cells B7 and C7. The subsequent entries in column B are the times at which the video camera sees the ball. These are one-thirtieth of a second apart. (Obviously, there is some uncertainty in just when in the first interval the ball begins to fall.) We call this time interval D t, or "delta_t" on the spreadsheet. To fill in the B column with these times automatically, we use a formula. First, in A4, write: "delta_t =". Now, in B4 enter "0.033333". Now, click on B4 again, and click "Insert, Name, Define". You will find that Excel will call B4 delta_t. This is what you want, so click OK.

Now you are ready to put in all the times. In B8, write "=B7+delta_t". The = (or +) is needed to tell Excel this is a formula, not text. Now this formula can be copied down, let’s do it for 20 rows (that’s enough for the falling ball.) Click on B8. Notice the cursor is a white cross, with a black border. As you move the cursor around in B8, on the edges it changes to the usual arrow, except that at the little black square on the bottom right-hand corner it goes to a + sign. While it is a + sign, drag it down with the mouse for twenty rows. This will automatically put in all the times. Of course, they will show large numbers of decimal places, which you don’t want. To rectify this, click on the "B" at the head of the column, to select the whole column. Then click Format, Style, Modify. You will find "General" is the default selection. Change it to "Number". The default is now 2 decimal places, you can change it if you wish, for present purposes it’s OK. Then click "OK" (twice) as you leave.

 The frame-by-frame distances can now be entered in column C. Perhaps it’s simplest to use centimeters here, and convert later. If you want to practice the routine before doing the experiment, here is a set of fake data to enter:

Having put these numbers in, we can plot them. Select the block of squares from B6 down to C26 or 27, or wherever your last distance entry was. (Notice you include the text cells containing "time’ and "distance".) Now click on ChartWizard (the little column graph on the Toolbar), then click "XY Scatter", then choose the topmost plot (the default) of the five displayed, which has no lines going through the points. Click "Next" to see the graph. Click "Next" again, and you can add names to the axes, etc., then just click "Finish".

 The reason not to choose one of the graphs showing lines through the points, is they draw lines exactly through the points, and we know these points have some errors of measurement in them. Assuming the errors are random (as likely up as down) we can get the best graph of a falling body by putting a "Trendline" through the points. To do this, right click on the data series on the chart. Then click "Add Trendline" and choose a polynomial of degree2. You should see a parabola passing near your points. (If you now right click on the trendline, then click "Format Trendline", you have the option of displaying the equation of the trendline. From this, you can find the acceleration, but it's better to approach this a bit more gradually, as described in the next paragraph.)

If you don’t like the default colors of the lines or the background, or the thickness of the trendline or the size of the diamonds denoting distance, right click in the appropriate place. For example, right clicking in the graph away from the curve, then clicking "Format Plot Area" you can choose a fill color, with shading effects if you like, and a different color for the border, which has adjustable thickness. (To edit an existing chart, it’s sometimes handy to bring on the Chart Toolbar, from View, Toolbars, Chart.)

The fake data give this graph:

Having plotted distance fallen against time, we can also plot how the speed is increasing with time, but that takes a little more thought. We can just find the speed in succeeding thirtieth of a second intervals by subtracting successive distances, and multiplying by 30 to see how far it would have fallen in one second at that speed. The tricky point is: what time, exactly, does that speed correspond to? It’s an average speed over an interval, say from 7/30 of a second to 8/30 of a second. At the initial instant, 7/30 second, it wasn’t going that fast (since it’s speeding up all the time) and by the time 8/30 of a second have elapsed, at the end of the interval, it’s falling faster than its average speed over the interval. We conclude that the average speed over the interval is close to the speed at the middle of the interval. In fact, since the speed is increasing at a steady rate, that is exactly correct.

So to plot how speed varies with time, we should write a column of new times, corresponding to the middles of the intervals. In D6, write "midtimes" and in E6 write "speed". For D7 and E7, we can still put in "0". Now in D8, write "=0.5*(B7+B8)". This gives the midpoint of the preceding time interval. In E8, write "=(C8-C7)/delta_t", this being the average speed over the preceding interval.

Now select D8 and E8 and copy them down, by dragging with the + cursor symbol on the bottom right-hand corner of the selecting boundary, as before.

 Next, plot a chart by selecting the block D6 to E27 or so, using the same chart options as above, except that in adding the trendline, choose the "linear" option. Finally, right click on the trendline, click "Format Trendline", "Options" and click "Display Equation on Chart". The displayed linear relationship between speed and time is the best fit to your data: it is the acceleration, the rate of increase of speed with time.