Spreadsheets are one of the most underrated tools available for doing and learning mathematics, especially calculus. At my college we include spreadsheets as a central tool for our Calculus I course and use them every chance we get. But as with all technology, there is the possibility of encountering a seemingly inexplicable glitch when using them even in a very tame situation.
Here’s one I encountered this week when setting up a spreadsheet to do an average velocity/instantaneous velocity problem. We started with a falling object whose position from the start point at time t is given in the following table:
The eventual goal is to compute the average velocity from t=2 to t=3, then t=2.5 and t=3, then t=2.9 and t=3, and so on, finally estimating the instantaneous velocity right at t=3. Actually, this is where the glitches started. The “25” in the third cell was supposed to be a “20” because I wanted to position data to be fit, exactly, by the function , which we were going to use to generate the position data not in the table. I caught this after an initial edit but decided it would be more interesting to proceed with the 25 there, then use my spreadsheet to get a power function trendline through the data, and go from there.
Go from there I did, and here’s the chart with the trendline:
Pretty close to , right? Well… not exactly. Here’s what we get when we use the trendline formula to generate the position data for value of time approaching t=3 from the left, and then the average velocity from those times to t=3:
What’s supposed to happen is that the position values approach the position attained by the book at t=3, and the average velocities stabilize toward a single number which represents the instantaneous velocity at t=3. But the little deviation in the t=2 position from the original table (25 instead of 20) throws the trendline off so that the position as time approaches 3 overshoots the actual position at t=3, and so we end up with average velocities that are spinning out of control. (-1781 m/sec is roughly 4000 miles per hour, for reference, and the direction is wrong to boot).
But, you say, this is no surprise, because the mistake in the original table had the position off by 5 meters from where you intended. But what’s funny is that if you go back and make the mistake in the original table smaller, even a lot smaller, you encounter the same effect. If you change the 25 in the t=2 cell to 20.1 — that’s just a difference of less than 4 inches from the intended position of 20 meters — the trendline changes to , and here’s what you get as time approaches 3:
As we close in on t=3, we still get the object rocketing upward!
What we learn here is that if you use a trendline for calculations, you really shouldn’t mix data from the trendline with data from the table which produced the trendline. In fact, the original trendline created using s(2) = 25 would predict s(3) ≈ 46.813, and when that value is used instead of 45 in the average velocity calculations you see the averages stabilize, although slowly, towards something like 31.2 m/sec.
But even then, if you took that trendline formula and found using the Power Rule (as we typically end up doing later in the course to tie algebraic diferentiation rules back to table calculations), you get , which is not what the average velocities are approaching in the table.
So spreadsheets are useful tools for learning mathematics, but for the kind of infinitesimal, close-up work that we have to do with calculus, error propagation becomes a viable course topic as the students are learning about limits.