If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel graphed trendline does not match derived equation
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 |
#2
|
|||
|
|||
Excel graphed trendline does not match derived equation
Keith
I can't reproduce your results without some data. Can you copy paste the X & Y values? "Keith" wrote in message ... 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 |
#3
|
|||
|
|||
Excel graphed trendline does not match derived equation
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" wrote in message ... 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 |
#4
|
|||
|
|||
Excel graphed trendline does not match derived equation
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 "Kelly O'Day" wrote: Keith I can't reproduce your results without some data. Can you copy paste the X & Y values? "Keith" wrote in message ... 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 |
#5
|
|||
|
|||
Excel graphed trendline does not match derived equation
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 "Keith" wrote in message ... 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 "Kelly O'Day" wrote: Keith I can't reproduce your results without some data. Can you copy paste the X & Y values? "Keith" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | MEGTOM | New Users | 5 | October 27th, 2005 03:06 AM |
Windows in Taskbar | Chevy | General Discussion | 8 | October 15th, 2004 03:57 PM |
Coverting Lotus 123 files to use with excel | Muffin1947 | General Discussion | 6 | June 20th, 2004 10:18 AM |
Trendline equation not giving correct results | Sudhanshu | Charts and Charting | 5 | February 6th, 2004 12:25 AM |
Trendline equation in chart is wrong | Iskandar Taib | Charts and Charting | 6 | November 27th, 2003 03:26 AM |