# Tag Archives: excel

## Spreadsheets and calculus: Proceed with caution

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 $s = 5t^2$, 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 $s = 5t^2$, 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 $y = 5.0089x^{1.9992}$, 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 $y'(3)$ 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 $y'(3) \approx 30.634$, 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.

Note: I used Numbers 09 for the charts and trendlines. I think Excel uses the same algorithm to produce power function trendlines as does Numbers, so this isn’t an Apple vs. Microsoft issue.

Filed under Calculus, Math, Teaching, Technology

## Estimating the digits in a Mersenne prime — for dummies

At the end of this post, I made a totally naive guess that the recently discovered candidate to be the $M_{45}$, the 45th Mersenne prime, would have 10.5 million digits. There was absolutely no systematic basis for that guess, but I did suggest having an office pool for the number of digits, so what I lack in mathematical sophistication is made up for by my instinct for good nerd party games. On the other hand, Isabel at God Plays Dice predicted 14.5 million digits based on a number theoretic argument. Since I am merely a wannabe number theorist, I can’t compete with that sort of thing. But I can make up a mean Excel spreadsheet, so I figured I’d do a little data plotting and see what happened.

If you make a plot of the number of digits in $M_n$, the nth Mersenne prime, going all the way back to antiquity, here’s what you get:

The horizontal axis is n and the vertical axis is the number of digits in $M_n$.

Admit it — one look at this plot and you’re itching to add some trendlines. Here’s what you get when you add both an exponential trendline (perhaps the obvious choice given the shape) and a 6th-degree polynomial:

The exponential one has a higher $R^2$ value, but that’s perhaps misleading because of the really good fit for all those low-digit Mersenne primes that happened prior to around $M_{30}$. We’ll take that issue up in a moment. But for now, let’s put those trendline equations to work. The exponential trendline would predict that $M_{45}$ would have a digit count of

$0.5867 e^{0.3897 \times 45} = 0.5867 e^{17.5365} \approx 24,233,786$

which is obviously rather a lot more than either my prediction or Isabel’s; and if you put in $x=45$ into the 6th-degree polynomial, you get a digit count of 11819349, which is in the ballpark of both my rough estimate and Isabel’s estimate.

It doesn’t make much sense, though, to include all Mersenne primes, since Mersenne primes didn’t even cross the 100-digit mark until $M_{13}$ in 1952. A more accurate idea — if you can call this kind of reasoning accurate in the first place — would be to run the numbers starting at around $M_{20}$ and seeing what we get. I’ll save that for later, unless somebody wants to beat me to it.