Excel question

I’m sure somebody out there is more knowledgeable in Excel than I am, so here’s something weird that happened on a recent calculus assignment that neither my students nor I have been able to figure out.

The students were sent to this web page and asked to enter the population data into a spreadsheet, rescaling the time variables so that the year 1790 was t = 0, 1800 was t = 10, and so on, up through the year 2000 (t=210). Then they were then asked to make an XY-plot of the data, which looks like this:

Then they were supposed to fit the XY-plot with a linear trendline, order-3 polynomial trendline, and then an exponential trendline. The linear and cubic trendlines are no problem, but when you go to add the exponential trendline, you can’t:

The options for power, log, and exponential are all unavailable. So, why is this? The best guess the students could come up with is that the data plot goes through the point (0,0), and since that never happens for a “pure” exponential function (something of the form y = Ae^(bx)), Excel won’t let you use exponentials. But that doesn’t explain why the power function option wasn’t available.

Technorati Tags: , , ,

1 Comment

Filed under Calculus, Math, Technology

One response to “Excel question

  1. MarkS

    It is possible that excel is actually being somewhat smart for a change. When getting an exponenial model for (x,y), you are actually finding a linear model for (x,log y), then solving for y. Likewise, when you get a power model for (x,y), you are really finding a linear model for (log x, log y). In both cases, Excel is noticing that it would have to take a log of zero, which it can’t do. If you throw out the first point (or make both values very small) then Excel will allow you to do an exponential fit or a power fit.