Excel graphed trendline does not match derived equation

[Keith]

I have a scatter-point chart and I have been able to plot the linear
trendline through those scatter points. I then formatted the linear
trendline and got the equation for the line.

However, I noticed that the y-intercept (7.9...) of the actual line that was
graphed by Excel is not the same as the y-intercept (8.9573) for the equation
that Excel derived from the graphed line. The actual Excel graphed line and
the line the equation appear to be parallel to each other. As a result,
entering data into the equation supposedly derived from the graphed line will
not result in the line that was graphed by Excel.

Thank you in advance for your help

 

[Kelly O'Day]

Keith

I can't reproduce your results without some data. Can you copy paste the X &
Y values?

(e-mail address removed)

 

[Mike Middleton]

Keith -

(1) Be sure you are using an XY (Scatter) chart type, not a Line chart type.

(2) If any of the X values are text, even the XY (Scatter) chart type will
use 1,2,3,... for the X values of the trendline.

To coerce seemingly-numeric text into numbers, copy a blank cell, select the
X data range, and choose Edit | Paste Special | Add.

(3) Another way to check your results is to use the INTERCEPT and SLOPE
worksheet functions.

- Mike
www.mikemiddleton.com

 

[Keith]

Kelly,

The linear equation that Excel produces for the following data set is:

y = -4E-05x + 8.9573

with an R^2 of:

0.5539

X-Axis (Square Foot) & Y-Axis (Trip Rate)

128993 5.03
135197 2.94
129000 2.88
90255 5.30
135197 3.89
130316 4.31
129044 3.76
135197 4.25
135197 2.91
120059 5.01
164558 1.26
178207 1.13
105700 2.60
164775 1.41
164775 1.84
168044 2.36
123173 1.46
165129 1.10
163900 1.96
165030 2.20
163268 1.41
167400 1.69
139325 3.80
163704 2.43
163268 1.42
160680 1.75
130019 2.52

If I use the trendline that is plotted for the above data for 169200 square
feet I would expect a trip rate of approximately 1.6 to 1.8 however the
equation for that same line gives me a trip rate of 2.19.

Thanks Kelly

Keith

 

[Mike Middleton]

Keith -

Use more significant digits in your calculations. On the chart, select the
trendline text-like box containing the equation, and on the formatting
toolbar repeatedly click the Increase Decimal button to get:

y = -0.000043175733348x + 8.957163001493400
R2 = 0.554182966050947

For X = 169200, Y = 1.651828919 (using worksheet functions).

- Mike
www.mikemiddleton.com

 

[Kelly O'Day]

Keith:

Thanks for sending the data.

I got an intercept of (8.9572) and slope of (-0.000043176) exactly like
you got.. I also got the same correlation coefficient.

To make sure that these results were valid, I used both the Excel Chart
liner trendline as well as Excel Intercept and Slope functions. Both methods
returned the exact same values.

I next used the formula Y = 8.9572 - 0.000043176* X to forecast your Y value
for an X of 169,200. I got 1.65, which plots right on the regression
trendline and is consistent with what you expect.

I am not sure how you got the 2.19. Can you double check the formula you
used to forecast Y.

As far as I can tell, your regression is working fine.


....Kelly

(e-mail address removed)