Lesson 9-I: Exponential Model Fitting with Excel

In Lesson 8, we saw that there is good theoretical reason to expect populations to grow according to exponential functions. (Actually, we'll see later that the reality is a good deal more subtle, but for now let's roll with it.) But the real world is a messy: we have measurement error, rounding error, probably our assumptions aren't exactly borne out in reality, etc.

Given that, we don't expect any exponential function to fit real-world data exactly. But we can ask, given real-world data we suspect of being exponential:

  1. Are the data really exponential?
  2. If so, what's the best-fitting exponential function to match the data?

The Mathematical Methods

The mathematical problem we need to solve is: given a bunch of data points (say, like the populations of Raleigh and Wake County:)

year Raleigh Wake County
1900 13643 54626
1910 19218 63229
1920 24418 75155
1930 37379 94757
1940 46879 109544
1950 65679 136450
1960 93931 169082
1970 122830 228453
1980 150255 301327


Which function of the form P(t)=P_0e^{rt} best matches the data?

This is actually a very similar problem to Lesson 2, where we asked, Which linear function best matches the data? As in that case, Excel provides a ready-made solution for us to use, called the trendline. To use this feature, we simply:

  1. Enter the data in Excel. We'll work with Raleigh's population. To make things just a bit simpler, let's change time to count "years since 1900":
  2. Plot the data on a scatter plot and select one of the data points:
  3. Right-click (control-click on a Mac) and "add trendline...".
  4. In the Trendline Panel, we see that there are several options. By default, "linear" is selected; for these data, the linear model looks okay but not great. To quantify this, select "display R-squared".
  5. Now select "exponential", and you'll see how much better the curve fits, and how much closer to 1 the value of R^2 has become.
  6. Click "display equation on chart" to get Excel to report the values of C and r that it thinks are best. In this case, it's C=13969 and r=.0307.
  7. We should translate back into our language: our exponential model hereĀ  is  P(t)=13696e^{.0307t}.


The Log-Transform Trick (Excel)

That was a bit messy, and as you can see, there's not much hope we could have done it by hand. There's another approach, too.

This relies on a function called the natural logarithm, which whatever else it does, has the following three nice properties:

 \ln (AB)=\ln A + \ln B "The log of a product is the sum of the logs."
\ln(e^t)=t "Natural log undoes the exponential."
e^{\ln(t)}=t "Exponential undoes natural log."

To carry out the log-transform fitting:

  1. Make a table in Excel which contains the data we want to fit, and also the log-transformed data:
  2. Plot the log-transformed data. :
  3. Get a (linear) trendline for the log-transformed data:

    The trendline is  \ln(P(t))=9.5446+.0307t
  4. Use the trendline for the log-transformed data as a power for e:
     P(t)=e^{\ln(P(t))}=e^{9.5446+.0307t}=e^{9.5446}e^{.0307t}=13969.0577 e^{.0307t}
  5. This is the exponential model.

The log-transform trick allows us to use linear techniques (which are computationally easier) and to use our eyes (which are good at seeing lines) to find exponential models when they're appropriate. Observe that we have the following correspondence between the parameters

log-transformed fit slope m intercept b
untransformed fit growth rateĀ   r=m initial population C=e^b

Now, try both techniques (Excel's exponential fit and the log-transform-and-linear-fit approach) to find the growth rate of Wake County's population 1900 -- 1980.


To say you've successfully completed this lesson, you should be able to do the following:

  1. Use Excel to generate a scatter plot from given data.
  2. Use Excel to plot a best-fit exponential and report its equation.
  3. Use Excel to compute the sum-of-squares measure to see how well a given exponential model fits given data, and to compare how well an exponential model fits as opposed to a linear model.
  4. Use the log-transform trick and Excel's trendline to find an exponential model.