Curve Fitting Exponentials

In my last post I learned you can’t always use the trend line formula created by the scatter plot chart. For the scenarios where the job taken over by robots doubled every 1.5 or 2 years, I wanted to get a formula to enable me to calculate jobs taken over every year and not every 1.5 or 2 years. I started out trying to create the fitted exponential formula by using the trend line in the scatter plot chart. I charted the x (time) at every 1.5 (or 2) years and y (unemployed #) values using the scatter plot and then I asked for a trend line and formula associated with it. Microsoft gave me an answer.

I thought, “Yay, I got the answer.”

Then I thought, let me test this out so I inputted my x’s through the fitted exponential formula to get a calculated y and then compared against the original set of numbers used to create the formula. The variances were huge! See tab 3 “Job loss example” in the embedded Excel file. Columns B through F shows the job loss doubling every 1.5 years and the resultant unemployment. Columns H through O, the top part, shows the attempt to find a fitted formula through a scatter plot chart and trend line. Then column Q through T takes that formula and attempts to recreate the results in columns B through F using the same set of x values to get at the y values. And there you can see the huge variance.

So there I learned you can’t use that formula suggested by the trend line if you want a perfectly fitted line. You will get way off. If you are okay with just a swag, then that’s fine but I wanted something more exact.

This was one of the reasons why my last post took so long to be written. I was trying to resolve the curve fitting. I did try changing the fixed constants provided by the trend line with the thought that the trend line formula did not show enough exact digits. So instead of 20,000,000 before the e, I tried 21,000,000 and found the variance was reduced a little. So then I tried 22,000,000 and found the variance reduced some more. Next was 23,000,000 and I found that I had overshoot. Then the next number was 22,900,000 and found the reduction going in the right direction. I did this for quite some time until I got a better number to use.

So from that exercise I realized I needed a formula with a more robust set of expanded digits, not a simplified one.

Some more thinking ensued.

I remembered curve fitting with LINEST formula in Excel but that was for straight lines that conform to the pattern y = mx +b. I vaguely recalled making a straight line out of a curve using log (or maybe natural log) but I couldn’t remember my algebra very well.

So I googled.

Somewhere out on the internet somebody (engineerexcel) had figured out how to apply LINEST to fit to an exponential curve.

Basically the thing to realize is that the exponential curve is in the form of

y = a*e^(bx)

Do natural log of both sides of that formula and you get

Ln(y) = ln(a) + bx

Or ln(y) = bx + ln(a) similar to y = mx + b

So instead of LINEST(y, x, true, false), you would use LINEST(ln(y),x, true, false) to get your fixed constants for curve fitting.

The second tab of the embedded Excel file located above outlines this thinking with hard numbers for you to follow.