Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Hi,
I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Use an "XY (Scatter)" chart.
When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Similarly, the bar and column charts assume that the x-axis is non-numerica
categories to be treated as 1,2,3,... if a trendline is requested. Excel provides no chart type that plots bar heights/lengths against a numberic axis. Jerry "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Jerry,
thanks for your reply! You are right about excel using 1,2,3 as x-values: I hadn't figured that one out! What do you mean by an "XY (Scatter)" chart? I'm still missing something he What should I do differently to have excel give me the right equation? Thanks "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Jerry,
I think I've figured out what your meant by "xy scatter"! I modified my data table the following way and got the right results: X Y 73 6.6 74 75 76 77 78 5.8 .... ... Thanks for your help. Jan M. "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
What Jerry means is don't use a column chart. It is the wrong kind of chart
to use to show relationships between two variables, even if you are not trying to generate statistics on it. Rebuild the chart and select one of the XY subtypes in step 1 of the chart wizard, or convert the chart using Chart Type on the Chart menu. There's no need to mess around with the data range. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jerry, I think I've figured out what your meant by "xy scatter"! I modified my data table the following way and got the right results: X Y 73 6.6 74 75 76 77 78 5.8 ... ... Thanks for your help. Jan M. "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Jon,
thanks for your input. I've tried changing the chart type with no results. What is it that you call an XY subtype? It may seems trivial to you, but I assure you that it is not to me (maybe because I'm using a french version). Thanks. Jan M. "Jon Peltier" wrote: What Jerry means is don't use a column chart. It is the wrong kind of chart to use to show relationships between two variables, even if you are not trying to generate statistics on it. Rebuild the chart and select one of the XY subtypes in step 1 of the chart wizard, or convert the chart using Chart Type on the Chart menu. There's no need to mess around with the data range. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jerry, I think I've figured out what your meant by "xy scatter"! I modified my data table the following way and got the right results: X Y 73 6.6 74 75 76 77 78 5.8 ... ... Thanks for your help. Jan M. "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Okay, I'll try to type more slowly. That's a joke.
In step 1 of the Chart Wizard, or in the Chart Type dialog, the top five chart types in the left list are Column, Bar, Line, Pie, and XY. Select the fifth (XY) as the chart type. Even in the French version, I'm sure the icons are in the same order. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jon, thanks for your input. I've tried changing the chart type with no results. What is it that you call an XY subtype? It may seems trivial to you, but I assure you that it is not to me (maybe because I'm using a french version). Thanks. Jan M. "Jon Peltier" wrote: What Jerry means is don't use a column chart. It is the wrong kind of chart to use to show relationships between two variables, even if you are not trying to generate statistics on it. Rebuild the chart and select one of the XY subtypes in step 1 of the chart wizard, or convert the chart using Chart Type on the Chart menu. There's no need to mess around with the data range. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jerry, I think I've figured out what your meant by "xy scatter"! I modified my data table the following way and got the right results: X Y 73 6.6 74 75 76 77 78 5.8 ... ... Thanks for your help. Jan M. "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Jon and Jerry,
I've found the XY (Scatter)! In a french version it is called "Nuages de points" (Clouds of dots). Very poetic isn't it? Thanks to both of you. Jan M. "Jon Peltier" wrote: What Jerry means is don't use a column chart. It is the wrong kind of chart to use to show relationships between two variables, even if you are not trying to generate statistics on it. Rebuild the chart and select one of the XY subtypes in step 1 of the chart wizard, or convert the chart using Chart Type on the Chart menu. There's no need to mess around with the data range. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jerry, I think I've figured out what your meant by "xy scatter"! I modified my data table the following way and got the right results: X Y 73 6.6 74 75 76 77 78 5.8 ... ... Thanks for your help. Jan M. "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Very nice. Is that what Frenchmen call it, or is that just Microsoft's
unique translation? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jon and Jerry, I've found the XY (Scatter)! In a french version it is called "Nuages de points" (Clouds of dots). Very poetic isn't it? Thanks to both of you. Jan M. "Jon Peltier" wrote: What Jerry means is don't use a column chart. It is the wrong kind of chart to use to show relationships between two variables, even if you are not trying to generate statistics on it. Rebuild the chart and select one of the XY subtypes in step 1 of the chart wizard, or convert the chart using Chart Type on the Chart menu. There's no need to mess around with the data range. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jerry, I think I've figured out what your meant by "xy scatter"! I modified my data table the following way and got the right results: X Y 73 6.6 74 75 76 77 78 5.8 ... ... Thanks for your help. Jan M. "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
Jon,
I can't speak for others, but I'm not aware of anyone calling a graph a "cloud"! Jan. "Jon Peltier" wrote: Very nice. Is that what Frenchmen call it, or is that just Microsoft's unique translation? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jon and Jerry, I've found the XY (Scatter)! In a french version it is called "Nuages de points" (Clouds of dots). Very poetic isn't it? Thanks to both of you. Jan M. "Jon Peltier" wrote: What Jerry means is don't use a column chart. It is the wrong kind of chart to use to show relationships between two variables, even if you are not trying to generate statistics on it. Rebuild the chart and select one of the XY subtypes in step 1 of the chart wizard, or convert the chart using Chart Type on the Chart menu. There's no need to mess around with the data range. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jerry, I think I've figured out what your meant by "xy scatter"! I modified my data table the following way and got the right results: X Y 73 6.6 74 75 76 77 78 5.8 ... ... Thanks for your help. Jan M. "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
Linear trendline:wrong equation
What's the word for snow, "neige"? Maybe that's the word they wanted, thing
flurry instead of scatter. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jon, I can't speak for others, but I'm not aware of anyone calling a graph a "cloud"! Jan. "Jon Peltier" wrote: Very nice. Is that what Frenchmen call it, or is that just Microsoft's unique translation? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jon and Jerry, I've found the XY (Scatter)! In a french version it is called "Nuages de points" (Clouds of dots). Very poetic isn't it? Thanks to both of you. Jan M. "Jon Peltier" wrote: What Jerry means is don't use a column chart. It is the wrong kind of chart to use to show relationships between two variables, even if you are not trying to generate statistics on it. Rebuild the chart and select one of the XY subtypes in step 1 of the chart wizard, or convert the chart using Chart Type on the Chart menu. There's no need to mess around with the data range. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jan M." wrote in message ... Jerry, I think I've figured out what your meant by "xy scatter"! I modified my data table the following way and got the right results: X Y 73 6.6 74 75 76 77 78 5.8 ... ... Thanks for your help. Jan M. "Jerry W. Lewis" wrote: Use an "XY (Scatter)" chart. When you selected a "Line" chart, you (by definition) told Excel that your x-axis was categorical instead of numeric, and that what you provided for the x-axis was a set of category labels that may or may not have numeric values. Why Excel would offer to fit a trendline in that circumstance is a mystery to me, but when it does, it uses x-values of 1,2,3,... and correctly calculates the regression of y against those assumed x-values. Jerry "Jan M." wrote: Hi, I created a bar chart in Excel from the following data: X Y 73 6.6 78 5.7 86 4.8 The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448 respectively. The resulting equation is Y = -0.136X + 16.448 which seems good enough to me. Then I added a linear trendline to the chart. Excel displayed the following equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding problem)!!! The data seemed farly linear to me, how come Excel can't come up with the right equation??? Thanks Jan M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Equation to refer to data value(s) and not the cell?? | Excel Discussion (Misc queries) | |||
Linear Trendline Equation Question | Charts and Charting in Excel | |||
how do I graph a linear equation? | Charts and Charting in Excel | |||
calculate non linear equation lines using excel | Excel Discussion (Misc queries) | |||
trendline - request power --> get linear | Excel Worksheet Functions |