Trendline is extremely wierd

**LennytheMeme** · 09-20-2017, 07:50 PM

I'm trying to display an exponential trend line for an assignment for my class, but the trend line is just going off into space.
Any idea on why it is doing this?
Capture.JPG

**sandy666** · 09-20-2017, 07:56 PM

Attach a sample workbook, not a picture. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**LennytheMeme** · 09-20-2017, 08:52 PM

Sorry about that, here is the workbook. I just put in a line to show what i believe what the line will look like and what I want. I did it in google sheets and i got what i wanted.

**MrShorty** · 09-20-2017, 09:55 PM

The limits of double precision strike again. Testing steps:
1) Display equation on chart, expand number format, enter coefficients into spreadsheet (if I were doing this thing for real, I would use the LOGEST() or LINEST() function to get equation coefficients, but this is quick testing)
2) Create formula in column G =$R$11*exp($R$12*D8) and copy down. Note that all values above year 1993 are #NUM. This is because the exp(0.356*1993) part of the function overflows the processor. I expect this is what is happening in the chart, and it is charting a very large number instead of erroring.

I don't know how you can change how the trendline utility computes the trendline values. With the coefficients in the spreadsheet, you can rearrange the equation:
ln(y)=ln(A)+B*t
compute ln(A)+B*t, then use the EXP() function to get the real values for the function. Or find some other calculation strategy/order of operations to avoid the overflow error.

That should explain the error, and suggest one way around the error. Is there more you need help with?