different results from linest function in Excel2000 and Excel2003

[bellarby]

I was happily using the following piece of code within VBA i
Excel2000:
Coefficients = WorksheetFunction.LinEst(YRange(), XPower(), , True)
This returned a reasonable set of co-efficients for a 4th to 7th orde
polynominals (we are using it for getting an equation from a set o
data).

However, upon 'upgrading' to Excel 2003 then the results are widel
different for the 5th, 6th and 7th order polynominals. I am not awar
of any settings which might effect this and have spent ages searchin
in the help and msdn but to no avail. Subsequently, I have changed bac
to Excel2000 and the results are good again.

Has anyone had the same problem?
Has anyone got any solution!?

regards

Jame

 

[hgrove]

bellarby wrote...

I was happily using the following piece of code within VBA in
Excel2000:
Coefficients = WorksheetFunction.LinEst(YRange(), XPower(), , True)
This returned a reasonable set of co-efficients for a 4th to 7th
order polynominals (we are using it for getting an equation from
a set of data).

However, upon 'upgrading' to Excel 2003 then the results are
widely different for the 5th, 6th and 7th order polynominals. I
am not aware of any settings which might effect this and have
spent ages searching in the help and msdn but to no avail.
Subsequently, I have changed back to Excel2000 and the
results are good again.

Has anyone had the same problem?
Has anyone got any solution!?

1. It's not a problem.

2. The solution is use Excel 2003, not earlier versions.

Prior to Excel 2003, Excel used an inferior numerical procedure i
LINEST, and for problems such as fitting high order polynomials, it'
coefficients weren't as accurate as they should have been. In Exce
2003, Microsoft changed to a more numerically robust procedure, an
LINEST's results are now more accurate. See

http://support.microsoft.com/default.aspx?scid=kb;en-us;828888&Product=xl2003

The point is that if Excel 2000 and Excel 2003 give differen
coefficients from LINEST, those given by Excel 2003 are almos
certainly more accurate in the sense of lower squared error betwee
fitted and actual y values. If you prefer the less accurat
coefficients generated by Excel 2000, that's your own affair, but yo
should realize that you're using almost certainly flawed figures

 

[hgrove]

bellarby wrote...

I was happily using the following piece of code within VBA in
Excel2000:
Coefficients = WorksheetFunction.LinEst(YRange(), XPower(), , True)
This returned a reasonable set of co-efficients for a 4th to 7th
order polynominals (we are using it for getting an equation from
a set of data).

However, upon 'upgrading' to Excel 2003 then the results are
widely different for the 5th, 6th and 7th order polynominals. I
am not aware of any settings which might effect this and have
spent ages searching in the help and msdn but to no avail.
Subsequently, I have changed back to Excel2000 and the
results are good again.

Has anyone had the same problem?
Has anyone got any solution!?

1. It's not a problem.

2. The solution is use Excel 2003, not earlier versions.

Prior to Excel 2003, Excel used an inferior numerical procedure i
LINEST, and for problems such as fitting high order polynomials, it'
coefficients weren't as accurate as they should have been. In Exce
2003, Microsoft changed to a more numerically robust procedure, an
LINEST's results are now more accurate. See

http://support.microsoft.com/default.aspx?scid=kb;en-us;828888&Product=xl2003

The point is that if Excel 2000 and Excel 2003 give differen
coefficients from LINEST, those given by Excel 2003 are almos
certainly more accurate in the sense of lower squared error betwee
fitted and actual y values. If you prefer the less accurat
coefficients generated by Excel 2000, that's your own affair, but yo
should realize that you're using almost certainly flawed figures

 

[bellarby]

The problem is though that Excel2003 gives worse results, much worse i
my case. The co-efficients from Excel2000 were fine for my purposes bu
are completely unusable from Excel2003. I appreciate that Microsoft hav
updated LINEST to make it more numerically robust but I am seeing th
opposite!

For example the const in a polynomial function shouldn't change much a
you increase the order of the polynominal. The values listed below sho
that for 6th and 7th order polynomials, the results from LINEST i
Excel2003 go widely wrong!

Excel2000
const
7th order 152.1000051
6th order 152.0990377
5th order 152.1732283
4th order 153.0223277

Excel2003
7th order 732.6942866
6th order 278.8403823
5th order 152.1732283
4th order 153.0223277


regards

Jame

 

[Jerry W. Lewis]

If you are seeing a difference between 2000 and 2003, you are almost
certainly in a situation where 2000 is incapable of giving the correct
answer, regardless of how much you like the answer that it does give.
See for example
http://groups.google.com/groups?threadm=cEe%[email protected]
and discussions of it, such as
http://groups.google.com/groups?threadm=3D81E207.6000506@no_e-mail.com

Your premise that the constant shouldn't change much as you increase the
order of the polynomial is incorrect; the more terms you add, the
greater the potential for trying to follow noise rather than trend. Try
plotting the data; either you are overfitting it (in which case you
should drop the higher order terms), or you are trying to add precision
to an exact relationship that LINEST in 2000 is incapable of providing.

While you are plotting the data, add a polynomial trendline of the same
order as you are trying to fit using LINEST. The chart polynomial
trendline has always used a high quality algorithm; will agree between
2000 and 2002, and will almost certainly confirm LINEST from 2003.

If you disagree, try posting the data (text in a reply to the group, not
an attachment) and what you think the answer should be.

Jerry