Linear Trend equation not working for actual values

[dayday123]

Hello -

I have seen a ton of posts on this topic but have yet to find a solution that actually works.

I plotted time series data on a graph and the trend line equation is showing as "y=1E+08x+1E+09"

Therefore I tried to get the actual value of the trend line by using this equation against my data but its not working.

For example based on this formula i got the following for the first 4 values in my time series

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]original data[/TD]
[TD]data from the equation[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1,124,041,004[/TD]
[TD]1,100,000,000 (1*10^8)(1)+(1*10^9)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1,353,546,669[/TD]
[TD]1,200,000,000 (1*10^8)(2)+(1*10^9)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1,592,063,714[/TD]
[TD]1,300,000,000 (1*10^8)(3)+(1*10^9)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1,477,367,226[/TD]
[TD]1,400,000,000 (1*10^8)(4)+(1*10^9)[/TD]
[/TR]
</tbody>[/TABLE]

But according to the trend line on the graph by time series 3 i should have already hit 1,500,000,000 and the formula only returns 1,300,000,000

What am i doing wrong?

Thank you so much for your help. I have been trying to find a solution for this for hours already

 

[joeu2004]

First, it you insist on copying the coefficients from the chart trendline formula, display the coefficients with much more precision.

I suggest using Scientific with 14 decimal points. That works for all magnitudes; and it displays the best precision (15 significant digits).

Right-click the trendline formula, click Format Trendline Label > Number > Scientific, and enter 14 in the Decimal Places field.

Second, it is usually better to enter a LINEST formula in Excel. Select 2 horizontal cells (e.g. F1:G1), and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=LINEST(B1:B4,A1:A4)

where B1:B4 is your original data, and A1:A4 are the corresponding x-axis values (presumably 1 through 4).

(You can omit the second parameter, A1:A4, if the x-axis values are truly 1 through 4.)

However, if the x-axis values are not 1 through 4, you probably have an incorrect trendline in the first place.

Third, keep in mind that the trendline, in this case, is a linear approximation of the data. It might not fit the original data very closely.

In fact, when I chart the data with x-axis values 1 through 4, I see two trends: an upward linear trend for the first 3 data points, and a downward linear trend for the last 2 data points.

So you might get a better fit by treating these as two data sets.

On the other hand, you say these are only the "first 4" values of a series of data. So my conclusion might be misleading.

 

[dayday123]

thank you so much for your reply joeu2004!!!

1) i didnt realize i could reformat the equation so thank you. i tried that method and it worked!
2) i succeeded with this formula as well!!

so happy i know this now. much obliged!