Linear Regression Chart Equation Seems Incorrect

[bs0d]

Maybe it's just late in the afternoon for me, but I have a data set with a linear regression trend line plotted through it. The data = percentages between 9 and 11%.

I can see the trend line passes through 9% over the next year if projected forward.

So the equation is:

 y = -2E-05x + 0.9913

I'm trying to supply a date (x) and solve for y (the percent). I'm using the serial value for date.

When I do this, I'm getting unexpected results.

For example:
1/1/2014 = 41640

y = (-0.00002 * 41640) + 0.9913

the result is 15.85%. This seems wrong to me because I can observe the trend line for 1/1/2014 is slightly above 10%.

What am I doing wrong here?

 

[joeu2004]

So the equation is:
y = -2E-05x + 0.9913

I'm trying to supply a date (x) and solve for y (the percent). I'm using the serial value for date. When I do this, I'm getting unexpected results. For example:
1/1/2014 = 41640
y = (-0.00002 * 41640) + 0.9913
the result is 15.85%. This seems wrong to me because I can observe the trend line for 1/1/2014 is slightly above 10%. What am I doing wrong here?

The coefficients you see in the trendline formula in the chart are rounded.

At a minimum, click on the trendline label in the chart, right-click and click on Format Trendline Label, then click on Number on the left, then click on Scientific and enter 14 decimal places, then click on Close.

The equation will look something like:

y = -1.65432109875432E-05x + 9.91265432109875E-01

If you use the coefficients as they appear now, you will probably get the results that you expect.

That is the poor-man's way of doing things. It would be better to use LINEST to get the coefficients directly (although they might be different).

 

[bs0d]

I haven't tested, but I bet you're right. Thanks for the tip on formatting the label. I'll also check out LINEST.