Error in formula displayed for linear and 2nd order curve fits inExcel 2003

[richard.wiens]

I have created trendlines for some pretty simple data using both 2nd
order polynomial and linear fits. In both cases, the displayed
formulas have their 1st and 2nd order coefficients wrong by a factor
of 10!! (I have read lots of posts about increasing precision but this
is not what I am up against). Has anyone seen this?

 

[Jon Peltier]

What version of Excel? Could you include the data in a follow up post (not
as an attachment), along with the coefficients you've computed?

- Jon

 

[richard.wiens]

I am using Office 2003 Standard, SP3

Here is the data for the linear trend:

Density 0.1 0.2 0.3
0.4 0.5 0.6
Throughput 3.125 2.726 2.378 2.061 1.742 1.452

The trendline formula displayed is y=-.3324x+3.4107

If use either SLOPE or just do a simple manual (delta y/delta x) slope
calcualtion, you get -3.324

For the 2nd order trend here is the data:

Density 0.1 0.2 0.3 0.4
Throughput 6.27 4.9 3.75 2.87

The formula displayed is y = 0.1225x^2 - 1.7475x + 7.8975. Using
another stats package I did a curve fit and got y=12.225x^2 - 17.475x
+ 7.8975, which yeilds correct ys for the given xs. Interestingly,
the 2nd order coefficient seems to be out by a factor of 100 (10^2),
while 1st order seems to be off by a factor of 10 (10^1).

Richard

 

[Jon Peltier]

Make an XY chart, not a line chart. A line chart counts categories as 1, 2,
3, etc., regardless of the numeric values in the cells.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


I am using Office 2003 Standard, SP3

Here is the data for the linear trend:

Density 0.1 0.2 0.3
0.4 0.5 0.6
Throughput 3.125 2.726 2.378 2.061 1.742 1.452

The trendline formula displayed is y=-.3324x+3.4107

If use either SLOPE or just do a simple manual (delta y/delta x) slope
calcualtion, you get -3.324

For the 2nd order trend here is the data:

Density 0.1 0.2 0.3 0.4
Throughput 6.27 4.9 3.75 2.87

The formula displayed is y = 0.1225x^2 - 1.7475x + 7.8975. Using
another stats package I did a curve fit and got y=12.225x^2 - 17.475x
+ 7.8975, which yeilds correct ys for the given xs. Interestingly,
the 2nd order coefficient seems to be out by a factor of 100 (10^2),
while 1st order seems to be off by a factor of 10 (10^1).

Richard

 

[richard.wiens]

Make an XY chart, not a line chart. A line chart counts categories as 1, 2,
3, etc., regardless of the numeric values in the cells.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______


I am using Office 2003 Standard, SP3

Here is the data for the linear trend:

Density           0.1           0.2         0..3
0.4             0.5           0.6
Throughput     3.125     2.726 2.378 2.061 1.742 1.452

The trendline formula displayed is y=-.3324x+3.4107

If use either SLOPE or just do a simple manual (delta y/delta x) slope
calcualtion, you get -3.324

For the 2nd order trend here is the data:

Density           0.1           0.2         0..3        0.4
Throughput      6.27      4.9    3.75 2.87

The formula displayed is y = 0.1225x^2 - 1.7475x + 7.8975.  Using
another stats package I did a curve fit and got y=12.225x^2 - 17.475x
+ 7.8975, which yeilds correct ys for the given xs.  Interestingly,
the 2nd order coefficient seems to be out by a factor of 100 (10^2),
while 1st order seems to be off by a factor of 10 (10^1).

Richard

Well that explains it perfectly (including my observation about the
coefficients)!

Doesn't seem intuitive to me that a line chart would do that but at
least now I know.

Thanks.

Richard