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.


0
JanM (9)
4/28/2006 2:16:01 PM
excel.charting 18370 articles. 0 followers. Follow

11 Replies
835 Views

Similar Articles

[PageSpeed] 50

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.
> 
> 
0
post_a_reply (1395)
4/28/2006 2:45:01 PM
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.
> > 
> > 
0
post_a_reply (1395)
4/28/2006 4:33:02 PM
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 
here: 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.
> > 
> > 
0
JanM (9)
4/28/2006 4:39:02 PM
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.
> > 
> > 
0
JanM (9)
4/28/2006 4:53:02 PM
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." <JanM@discussions.microsoft.com> wrote in message 
news:F53C98AA-0D52-4238-B05C-8139A27C7B3E@microsoft.com...
> 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.
>> >
>> > 


0
jonxlmvpNO (4558)
4/28/2006 5:21:14 PM
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." <JanM@discussions.microsoft.com> wrote in message 
> news:F53C98AA-0D52-4238-B05C-8139A27C7B3E@microsoft.com...
> > 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.
> >> >
> >> > 
> 
> 
> 
0
JanM (9)
4/28/2006 6:39:02 PM
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." <JanM@discussions.microsoft.com> wrote in message 
news:E884BF02-32E5-491C-B9F6-C762E00F417A@microsoft.com...
> 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." <JanM@discussions.microsoft.com> wrote in message
>> news:F53C98AA-0D52-4238-B05C-8139A27C7B3E@microsoft.com...
>> > 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.
>> >> >
>> >> >
>>
>>
>> 


0
jonxlmvpNO (4558)
4/28/2006 7:13:00 PM
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." <JanM@discussions.microsoft.com> wrote in message 
> news:F53C98AA-0D52-4238-B05C-8139A27C7B3E@microsoft.com...
> > 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.
> >> >
> >> > 
> 
> 
> 
0
JanM (9)
4/28/2006 7:19:02 PM
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." <JanM@discussions.microsoft.com> wrote in message 
news:488EE8AA-FC5E-482F-B784-2429873D2F7F@microsoft.com...
> 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." <JanM@discussions.microsoft.com> wrote in message
>> news:F53C98AA-0D52-4238-B05C-8139A27C7B3E@microsoft.com...
>> > 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.
>> >> >
>> >> >
>>
>>
>> 


0
jonxlmvpNO (4558)
4/28/2006 9:24:56 PM
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." <JanM@discussions.microsoft.com> wrote in message 
> news:488EE8AA-FC5E-482F-B784-2429873D2F7F@microsoft.com...
> > 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." <JanM@discussions.microsoft.com> wrote in message
> >> news:F53C98AA-0D52-4238-B05C-8139A27C7B3E@microsoft.com...
> >> > 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.
> >> >> >
> >> >> >
> >>
> >>
> >> 
> 
> 
> 
0
JanM (9)
5/1/2006 8:48:01 PM
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." <JanM@discussions.microsoft.com> wrote in message 
news:A3FDEB5D-014D-47CC-BE5F-980441A110DF@microsoft.com...
> 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." <JanM@discussions.microsoft.com> wrote in message
>> news:488EE8AA-FC5E-482F-B784-2429873D2F7F@microsoft.com...
>> > 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." <JanM@discussions.microsoft.com> wrote in message
>> >> news:F53C98AA-0D52-4238-B05C-8139A27C7B3E@microsoft.com...
>> >> > 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.
>> >> >> >
>> >> >> >
>> >>
>> >>
>> >>
>>
>>
>> 


0
jonxlmvpNO (4558)
5/1/2006 9:25:25 PM
Reply:

Similar Artilces:

Is it possible to generate a chart from a formula. or: Can I define and plot my own Trendline?
I'd like to generate a circle on a chart by entering the radius of the circle into a cell. Thanks in advance for any help, Greg Suppose the radius is in cell A1. Then, in B1 enter the formula =ROW()-1. In C1 enter =B1/180*PI(), in D1, =$A$1*COS(C1), and in E1 =$A$1*SIN(C1). Copy row 1 to 2:361. Plot D1:E361 in a XY Scatter chart. Once done, adjust the chart so that the x and y axis have the same min/max values. Also, select the plot area and extend it as close to the chart area as possible. Finally, select any cell. Then, hold down SHIFT and select the chart. Now, select...

wrong settings
i tried retrieving my e-mails from another server. From: "bert" <bert@discussions.microsoft.com> Subject: mail Date: Saturday, January 23, 2010 10:56 PM Hi, The highlighted message in my inbox has just started to disappear each time I log off? (And are not in the Delete file either?) Thanks in anticipation "KUJA " <r.kuja@live.com> wrote in message news:6E9809AD-046C-4FDE-A871-0588A3C6E9EE@microsoft.com... >i tried retrieving my e-mails from another server. > From: "bert" <bert@discussions.microsoft.com> > Subject: ma...

Am I doing something wrong here?
I'm creating a dynamic dist group using the following code: new-dynamicdistributiongroup -Name DGroup8 -RecipientContainer "domain.com/users" -OrganizationalUnit Users -RecipientFilter {(RecipientType -eq 'usermailbox') -and -not(Name -like "recmoved user")} So far so good, I see a dynamic list show up in the users container and all is well. However, when I run the following commands I see that the list is populated with users from all of the domain: $testing=Get-DynamicDistributionGroup -Identity DGroup8 Get-Recipient -Filter $testing.recipientfilter T...

OFX Web Statements Downloaded to Wrong Account
I just upgraded from Money2002 to 2005. When I now download my bank checking account statement it is downloaded to the wrong account, a credit card account from a different bank. How do I get it to download to the correct account. I don't even get a window allowing me to choose the account when downloaded. In microsoft.public.money, arkbagco wrote: >I just upgraded from Money2002 to 2005. When I now download my bank checking >account statement it is downloaded to the wrong account, a credit card >account from a different bank. How do I get it to download to the correct ...

Wrong Recording of Short Sell and Sell to Open transactions
I am using Money 2005 and have a problem that is driving me nuts. I downloaded investment transactions from Scottrade as a Direct Statement (reading this statement is itself horrendous, but that is for another day). My transactions include several Short Sell and Option Sell transactions. For some (many but not all) of these transactions, the Cash Account register shows the amount being deducted from the balance () instead of being added to the balance (Deposit column). That is, the amount shows up in the Payment column rather than the Deposit column. I spent several hours to re-enter the ...

equation of a curve
I created a simple curve on excel using data points.How can I get the polynomial equation of the curve ? Hi Naty Right click on one of your data points and select Add trendline. The trendline dialog boxes offers a number of options for type of trendline, showing equations in chart etc. HTH Martin Or you can use LINEST; see http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Naty" <Naty@discussions.microsoft.com> wrote in message news:F27FCC10-F36D-4A8A-A48E-502B5D462A77@microsoft...

from date format convert to text format is wrong
Hello All, I download from SAP to excel a database. But unfortunately I can't convert date cells to text. For Example: 2010.02.01 and if I convert to text format i'll get 40181 data all cells. I have tried to copy my worksheet to other worksheet with copy paste special only values, but the result is same. Please help! Regards, Nooris In XL dates are actually stored as numbers. The number is the number of days that has elapsed since Jan 1, 1900. So for example if you type in the number 40181 and format the cell as date you will see that the date is Jan 2, 2010. -...

What Am I Doing Wrong?
The only option I get results for is true and false. Well actually if the Dial-In permission is set to "Allow access" i get true. If it's set to "Deny access" I get false but when it's set to "Control access through policy" I get nothing and this is what is frustrating, since I'm using null and it's still not working. foreach ($item in $results) { $myUser = $item.GetDirectoryEntry() write-Host ($myUser.displayname + $myUser.msNPAllowDialin) if ( $myUser.msNPAllowDialin -eq $null ) { ...

Linear Programming
In Excel how do you set up a 3 variable linear programming problem on a spreadsheet with a a number of constaints using Solver? Franklin - See the tutorials and examples at www.solver.com. Or, search your drive for the file solvsamp.xls, which is installed when Solver is installed and which has a 3-variable nonlinear example that is easily converted to linear. - Mike www.mikemiddleton.com +++++++++++++++++++ "Franklin" <anonymous@discussions.microsoft.com> wrote in message news:0c5a01c500bf$8f5038c0$a501280a@phx.gbl... > In Excel how do you set up a 3 variable ...

Trendline coefficients
I have fit a series of data using a 5th order polynomial from the trendline dialog. Using the coefficients displayed in the equation, the curve is not faithfully reconstituted. Is it possible to export the trendline coefficients to arbitrary significant figures? Select the trendline equation on the greaph and format the number of significant figures "MrUniverseman" wrote: > I have fit a series of data using a 5th order polynomial from the trendline > dialog. Using the coefficients displayed in the equation, the curve is not > faithfully reconstituted. Is it po...

wrong received times in outlook
I have two systems running Outlook 2002 that will show a received time two hours prior to the time that the messages was actually received. An email is received at say 10:30 am and it will show a received time of 8:30 am in the outlook window. There is only one time zone set up by default when the software was installed. Any fixes? ...

linear and exponential graphs and x,y intercepts
I have two lines one linear (y=mx+c) and one exponential (y=c^bx) and they intercept each other. I believe it is possible to calculate where the lines intercept. Although I can do this by graphing the data, it takes time and allows for user error so a calculation would speed things along greatly. The problem: So for the first line using the equation y=mx+c the numbers for the 13kPa line are y=0.0021x+9e-16 And for the exponential line (y=c^bx) which we produce to using the myograph is y=0.0022^0.0046x (^=Exp) I know that the two lines intercept at 1613. But no matter what I try I can&...

how do I graph a linear equation?
I want to graph the linear equation y = 2x + 4 in microsoft excel. How do I do this? On my TI-83 Plus I could easily do this. I also know I could create a data table and use the table of values to chart the line in excel. But I want just enter the equation in excel and have excel graph it. Thanks. On Sun, 9 Jan 2005, in microsoft.public.excel.charting, Throstle <Throstle@discussions.microsoft.com> said: >I want to graph the linear equation y = 2x + 4 in microsoft excel. How do I >do this? On my TI-83 Plus I could easily do this. I also know I could create >a data table and ...

how to draw a trendline using two set of data?
Dear All, How to draw a trendline using two set of data (same x-value but different in y-value) thanks Cheers Do you want two trendlines, one for each data set? The Add Trendline dialog (Chart menu > Add Trendline) has a listbox to allow selection of a series. Do you want to combine the data into one series? You need to define a series that contains all of the points. If your two separate series have these series formulas: =SERIES(Sheet1!$C$4,Sheet1!$B$5:$B$12,Sheet1!$C$5:$C$12,1) =SERIES(Sheet1!$D$4,Sheet1!$B$5:$B$12,Sheet1!$D$5:$D$12,2) you need to add a series with this formula:...

Installation, "Wrong OS or OS version for application
Trying to install Money 2004, insert the CD and I get the disclaimer stating it may need additional componants and also stating anti-virus software may get in the way. I click OK and the error that comes up next is, "Installation of windows installer was not successful. Please install it manually by running instmsiu.exe from your Money CD. Then retry Money 2004 Setup.exe. I click on instmsiu.exe and the error message that comes up promptly reads as, "Wrong OS or OS version for application." I am, indeed, using Windows XP home edition--the rest of the minimum req&#...

annoying wrong dateformat
I live in Belgium but have a UK installation on my Mac. When I type in 11/2 as in February, Excel changes it to 2/11 as in November 2nd. My Formats are correct in 'International' in my System Preferences. Any ideas? It's very annoying. On 2/17/08 4:20 AM, in article ee8dc0e.-1@webcrossing.caR9absDaxw, "ilgrauls@officeformac.com" <ilgrauls@officeformac.com> wrote: > I live in Belgium but have a UK installation on my Mac. When I type in 11/2 as > in February, Excel changes it to 2/11 as in November 2nd. > My Formats are correct in 'International' i...

Logarithmic Trendline
Ok, I apologize if this is a stupid question, but I'm really confused about a result from Excel's trendline options. My data is below: X Y -0.15 -1.05 -0.14 -0.98 -0.13 -0.65 -0.12 -0.6 -0.11 -0.33 -0.10 -0.3 -0.09 -0.2475 -0.08 -0.22 -0.07 -0.1925 -0.06 -0.12 -0.05 -0.1 -0.04 -0.08 -0.03 -0.03 -0.02 -0.02 -0.01 -0.01 0.00 0 0.01 0.005 0.02 0.01 0.03 0.015 0.04 0.02 0.05 0.025 0.06 0.03 0.07 0.035 0.08 0.04 0.09 0.045 0.10 0.05 0.11 0.055 0.12 0.06 0.13 0.065 0.14 0.07 0.15 0.075 You can see from the data that the rate of change in the Y values increases quickly as X increases, henc...

Outlook 2003 dials wrong connection
I am helping a friend. She uses Outlook 2003 on XP SP2. Up to yesterday she had one ISP with an email account at that ISP. The ISP name is Freeserve. She wanted another email address for work and decided to sign up with another ISP, Tiscali, to get a new email. She uses pay as you go dialup with both Freeserve and Tiscali. The new Tiscali connection is the default connection for IE and shows as the default connection in Connect To|Show All Connections. Indeed if not connected, going to IE and requesting an online page brings the dial-up connection dialog for Tiscali. With Outlook 2003 h...

using linear / non linear graphs to extract results
Hi, A frequent function of my job is to analyse data on graphs/charts to extract data for engineering data , i.e power / temperature/flowrates etc etc Is the a microsoft based programe i.e. Excel/Access that is capable of understanding data on a scaled X-Y axis that if an X value was entered in a cell, the programme would extract a corresponding Y value from a line on a graph ? If anyone has any woring examples of such a thing, I'd appreciate a link Thx Hi, If there is a linear relationship between X and Y use the function FORECAST It a power series is needed use LINEST - see examp...

Page Border printing wrong
I've created 26 documents for our school. Each has a different page border. In print preview and at printing the bottom border is cut in half. It appears correctly on the screen. Tried eliminating margins, adjusting border size and does not correct the error. Any help? Thanks See http://www.word.mvps.org/FAQs/Formatting/BottomsDontPrint.htm. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. lchaussee wrote: > I've created 26 documents for...

Linear Regression
I have five numbers in: A1,B1,C1,D1,E1 Is there a function that will give me the linear regression of those numbers that can be put in D1, with charting etc? TIA Linear regression works with ordered pairs. row 1: 1,2,3,4,5 row 2: 2,4,6,8,<formula> formula in E2 : =FORECAST(E1,A2:D2,A1:D1) will return 10 Any use? best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Saxman" <john.h.williamsREMOVETHIS@btinternet.com> wrote in message news:XpidnXpInZMDMx7WnZ2dnUVZ8m1i4p2d@bt.com... > I have five numbers in: &...

2008 tax settings wrong in M07.
In M04 under Taxes was a sub-menu item called Tax Settings, and within that sub-menu was something called "Set your filing status and view tax rates by income bracket." I see that in M07 you can still select a filing status: Single, etc., to Custom, but I don't see anywhere to specify what "Custom" is, nor anywhere to verify the numbers that Money is using to estimate my taxes: rates, income brackets, phase outs, etc. Furthermore, the Standard Deduction and Exemption shown in the Summary for 2008 are $5150 and $3300, which are incorrect. (They should be $5450 ...

Wrong mailbox
Just this past week I am getting all most of my mail in the Outlook Express Inbox when it is open at the same time as Outlook. This just started and I am using Outlook as my default carrier. What is causing this. I only keep OE open because I have a Hotmail account and it doesn't automatically download into Outlook. PatK <vegaspatk@cox.net> wrote: > Just this past week I am getting all most of my mail in the Outlook > Express Inbox when it is open at the same time as Outlook. This just > started and I am using Outlook as my default carrier. What is > causing th...

cell to cell multiplications wrong in Excel (28.22 x 27=761.90)
using type accounting to 2 decimal places, gives me the above 761.90... but when i type it in a single cell (=28.22 x 27) i get the correct answer 761.94 when still using accounting type to 2 decimal places...........why is this rob Depends upon the underlying value of the cells that contain the formatted 28.22 and 27. If I have a cell with 28.2188 formatted as Accounting it shows 28.22 Another cell with 26.998123 formatted as Accounting it shows 27.00 Sum of the two cells is 761.85 Read the info here at John McGimpsey's site. http://www.mcgimpsey.com/excel/pennyoff.html Gord Di...

Linear Equations
I am having difficulty graphing a linear equation in two variables by using three ordered pair 1/2 y = 2x + My ordered pair x 0 1 y 2 6 10 see as follow Pairs x (0, 2 (1, 6 (2, 10 I am unable to graph this problem and need help!!! Just select the range containing the ordered pairs and Insert|Chart. Jerry Angela Mitchell wrote: > I am having difficulty graphing a linear equation in two variables by using three ordered pairs > > 1/2 y = 2x + 1 > > My ordered pairs > x 0 1 2 > y 2 6 10 see as follows > > Pairs: > x y > (0, 2) >...