software

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
Keith

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

quot;Keithquot; gt; wrote in message
...
gt;I have a scatter-point chart and I have been able to plot the linear
gt; trendline through those scatter points. I then formatted the linear
gt; trendline and got the equation for the line.
gt;
gt; However, I noticed that the y-intercept (7.9...) of the actual line that
gt; was
gt; graphed by Excel is not the same as the y-intercept (8.9573) for the
gt; equation
gt; that Excel derived from the graphed line. The actual Excel graphed line
gt; and
gt; the line the equation appear to be parallel to each other. As a result,
gt; entering data into the equation supposedly derived from the graphed line
gt; will
gt; not result in the line that was graphed by Excel.
gt;
gt; Thank you in advance for your help
gt;
gt;
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

quot;Keithquot; gt; wrote in message
...
gt;I have a scatter-point chart and I have been able to plot the linear
gt; trendline through those scatter points. I then formatted the linear
gt; trendline and got the equation for the line.
gt;
gt; However, I noticed that the y-intercept (7.9...) of the actual line that
gt; was
gt; graphed by Excel is not the same as the y-intercept (8.9573) for the
gt; equation
gt; that Excel derived from the graphed line. The actual Excel graphed line
gt; and
gt; the line the equation appear to be parallel to each other. As a result,
gt; entering data into the equation supposedly derived from the graphed line
gt; will
gt; not result in the line that was graphed by Excel.
gt;
gt; Thank you in advance for your help
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) amp; Y-Axis (Trip Rate)

1289935.03
1351972.94
1290002.88
902555.30
1351973.89
1303164.31
1290443.76
1351974.25
1351972.91
1200595.01
1645581.26
1782071.13
1057002.60
1647751.41
1647751.84
1680442.36
1231731.46
1651291.10
1639001.96
1650302.20
1632681.41
1674001.69
1393253.80
1637042.43
1632681.42
1606801.75
1300192.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

Keithquot;Kelly O'Dayquot; wrote:

gt; Keith
gt;
gt; I can't reproduce your results without some data. Can you copy paste the X amp;
gt; Y values?
gt;
gt;
gt;
gt;
gt; quot;Keithquot; gt; wrote in message
gt; ...
gt; gt;I have a scatter-point chart and I have been able to plot the linear
gt; gt; trendline through those scatter points. I then formatted the linear
gt; gt; trendline and got the equation for the line.
gt; gt;
gt; gt; However, I noticed that the y-intercept (7.9...) of the actual line that
gt; gt; was
gt; gt; graphed by Excel is not the same as the y-intercept (8.9573) for the
gt; gt; equation
gt; gt; that Excel derived from the graphed line. The actual Excel graphed line
gt; gt; and
gt; gt; the line the equation appear to be parallel to each other. As a result,
gt; gt; entering data into the equation supposedly derived from the graphed line
gt; gt; will
gt; gt; not result in the line that was graphed by Excel.
gt; gt;
gt; gt; Thank you in advance for your help
gt; gt;
gt; gt;
gt;
gt;
gt;

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

quot;Keithquot; gt; wrote in message
...
gt; Kelly,
gt;
gt; The linear equation that Excel produces for the following data set is:
gt;
gt; y = -4E-05x 8.9573
gt;
gt; with an R^2 of:
gt;
gt; 0.5539
gt;
gt; X-Axis (Square Foot) amp; Y-Axis (Trip Rate)
gt;
gt; 128993 5.03
gt; 135197 2.94
gt; 129000 2.88
gt; 90255 5.30
gt; 135197 3.89
gt; 130316 4.31
gt; 129044 3.76
gt; 135197 4.25
gt; 135197 2.91
gt; 120059 5.01
gt; 164558 1.26
gt; 178207 1.13
gt; 105700 2.60
gt; 164775 1.41
gt; 164775 1.84
gt; 168044 2.36
gt; 123173 1.46
gt; 165129 1.10
gt; 163900 1.96
gt; 165030 2.20
gt; 163268 1.41
gt; 167400 1.69
gt; 139325 3.80
gt; 163704 2.43
gt; 163268 1.42
gt; 160680 1.75
gt; 130019 2.52
gt;
gt; If I use the trendline that is plotted for the above data for 169200
gt; square
gt; feet I would expect a trip rate of approximately 1.6 to 1.8 however the
gt; equation for that same line gives me a trip rate of 2.19.
gt;
gt; Thanks Kelly
gt;
gt; Keith
gt;
gt;
gt;
gt;
gt;
gt; quot;Kelly O'Dayquot; wrote:
gt;
gt;gt; Keith
gt;gt;
gt;gt; I can't reproduce your results without some data. Can you copy paste the
gt;gt; X amp;
gt;gt; Y values?
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Keithquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have a scatter-point chart and I have been able to plot the linear
gt;gt; gt; trendline through those scatter points. I then formatted the linear
gt;gt; gt; trendline and got the equation for the line.
gt;gt; gt;
gt;gt; gt; However, I noticed that the y-intercept (7.9...) of the actual line
gt;gt; gt; that
gt;gt; gt; was
gt;gt; gt; graphed by Excel is not the same as the y-intercept (8.9573) for the
gt;gt; gt; equation
gt;gt; gt; that Excel derived from the graphed line. The actual Excel graphed line
gt;gt; gt; and
gt;gt; gt; the line the equation appear to be parallel to each other. As a
gt;gt; gt; result,
gt;gt; gt; entering data into the equation supposedly derived from the graphed
gt;gt; gt; line
gt;gt; gt; will
gt;gt; gt; not result in the line that was graphed by Excel.
gt;gt; gt;
gt;gt; gt; Thank you in advance for your help
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
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

quot;Keithquot; gt; wrote in message
...
gt; Kelly,
gt;
gt; The linear equation that Excel produces for the following data set is:
gt;
gt; y = -4E-05x 8.9573
gt;
gt; with an R^2 of:
gt;
gt; 0.5539
gt;
gt; X-Axis (Square Foot) amp; Y-Axis (Trip Rate)
gt;
gt; 128993 5.03
gt; 135197 2.94
gt; 129000 2.88
gt; 90255 5.30
gt; 135197 3.89
gt; 130316 4.31
gt; 129044 3.76
gt; 135197 4.25
gt; 135197 2.91
gt; 120059 5.01
gt; 164558 1.26
gt; 178207 1.13
gt; 105700 2.60
gt; 164775 1.41
gt; 164775 1.84
gt; 168044 2.36
gt; 123173 1.46
gt; 165129 1.10
gt; 163900 1.96
gt; 165030 2.20
gt; 163268 1.41
gt; 167400 1.69
gt; 139325 3.80
gt; 163704 2.43
gt; 163268 1.42
gt; 160680 1.75
gt; 130019 2.52
gt;
gt; If I use the trendline that is plotted for the above data for 169200
gt; square
gt; feet I would expect a trip rate of approximately 1.6 to 1.8 however the
gt; equation for that same line gives me a trip rate of 2.19.
gt;
gt; Thanks Kelly
gt;
gt; Keith
gt;
gt;
gt;
gt;
gt;
gt; quot;Kelly O'Dayquot; wrote:
gt;
gt;gt; Keith
gt;gt;
gt;gt; I can't reproduce your results without some data. Can you copy paste the
gt;gt; X amp;
gt;gt; Y values?
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Keithquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have a scatter-point chart and I have been able to plot the linear
gt;gt; gt; trendline through those scatter points. I then formatted the linear
gt;gt; gt; trendline and got the equation for the line.
gt;gt; gt;
gt;gt; gt; However, I noticed that the y-intercept (7.9...) of the actual line
gt;gt; gt; that
gt;gt; gt; was
gt;gt; gt; graphed by Excel is not the same as the y-intercept (8.9573) for the
gt;gt; gt; equation
gt;gt; gt; that Excel derived from the graphed line. The actual Excel graphed line
gt;gt; gt; and
gt;gt; gt; the line the equation appear to be parallel to each other. As a
gt;gt; gt; result,
gt;gt; gt; entering data into the equation supposedly derived from the graphed
gt;gt; gt; line
gt;gt; gt; will
gt;gt; gt; not result in the line that was graphed by Excel.
gt;gt; gt;
gt;gt; gt; Thank you in advance for your help
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;