#### 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.

11 Replies
2142 Views

Similar Articles

[PageSpeed] 53

```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
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
4/28/2006 4:33:02 PM
```Jerry,

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
....   ...

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
> ...   ...
>
>
> 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,

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,
>
>
> 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

- 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

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...

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...

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) >...