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.

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.