Remember Me?

#1
March 20th 05, 08:43 PM
 byundt Posts: n/a
LINEST bug with cubic polynomials in Excel 2003

LINEST in Excel 2003 SP1 finds incorrect values for regression coefficients
of cubic polynomials when the values of x are determined by a formula. Excel
97, 2000 and 2002 perform the calculation correctly.

To reproduce this problem:
1) Put the following labels and polynomial coefficients in cells A1:B4
a3 19
a2 23
a1 2
a0 81

2) Put the values of x in cells A9:C29
A9=-1.0
A10=A9+0.1 (copy this formula down)
B9=A9^2 (copy this formula down)
C9=A9^3 (copy this formula down)

3) Put the values of y in cells D929
D9=B\$4+B\$3*A9+B\$2*A9^2+B\$1*A9^3 (copy this formula down)

4) Select cells F9:I13 and array enter the following formula:
=LINEST(D929,A9:C29,TRUE,TRUE)

Note that LINEST in Excel 2003 returns incorrect values for a0 and a2. This
error is consistent even if I change polynomial coefficients in B1:B4. Excel
97, 2000 and 2002 all return the correct values, however.

If the formulas in A9:A29 are replaced by their values, then LINEST works
correctly. Also, if one of the rows of data is deleted (or repeated) then
LINEST works correctly.

Bottom line: I have an acceptable workaround for the time being--but
Microsoft has a bug that needs to be fixed.

#2
March 21st 05, 02:08 PM
 Jerry W. Lewis Posts: n/a

Old news. There are previously reported anomalies, suggesting that
LINEST/LOGEST coefficient estimates that are exactly zero in 2003 should
be looked on with some suspicion.
Apparently this is an example of MS being too helpful, as in the
difference between =A29-1 and =(A29-1) with your data.

As previously noted, you can identify the issue by an estimated
coefficient that is exactly zero. In the referenced thread I suggested
some ways to slightly perturb the model in order to avoid this
helpfulness gone wrong (also why you get different results from slightly
different formulations of the problem). In your case, you could also
simply reduce to
=LINEST(D928,A9:C28,,TRUE)

"If the formulas in A9:A29 are replaced by their values" is not entirely
clear. Do you mean Edit|Copy followed by Edit|Paste Special|Values, or
hand entering the values? They will not be the same, your increment
(0.1) has no exact binary representation, and hence must be
approximated. As a result, A19 is -1.38777878078145E-16 instead of
zero, and A29 is slightly less than 1 as shown by (A29-1). You can
reduce the accumulation of approximations by using
=(ROW()-19)/10
in A9:A29

Also, you are working harder than you need to by directly calculating
B9:C29 when you could use
=LINEST(D928,A9:A28^{1,2,3},,TRUE)

Jerry

incomplete.
1) specifies 8 values to fill 16 cells in an unspecified way.

byundt wrote:

LINEST in Excel 2003 SP1 finds incorrect values for regression coefficients
of cubic polynomials when the values of x are determined by a formula. Excel
97, 2000 and 2002 perform the calculation correctly.

To reproduce this problem:
1) Put the following labels and polynomial coefficients in cells A1:B4
a3 19
a2 23
a1 2
a0 81

2) Put the values of x in cells A9:C29
A9=-1.0
A10=A9+0.1 (copy this formula down)
B9=A9^2 (copy this formula down)
C9=A9^3 (copy this formula down)

3) Put the values of y in cells D929
D9=B\$4+B\$3*A9+B\$2*A9^2+B\$1*A9^3 (copy this formula down)

4) Select cells F9:I13 and array enter the following formula:
=LINEST(D929,A9:C29,TRUE,TRUE)

Note that LINEST in Excel 2003 returns incorrect values for a0 and a2. This
error is consistent even if I change polynomial coefficients in B1:B4. Excel
97, 2000 and 2002 all return the correct values, however.

If the formulas in A9:A29 are replaced by their values, then LINEST works
correctly. Also, if one of the rows of data is deleted (or repeated) then
LINEST works correctly.

Bottom line: I have an acceptable workaround for the time being--but
Microsoft has a bug that needs to be fixed.

#3
March 21st 05, 02:13 PM
 Jerry W. Lewis Posts: n/a

ld news. There are previously reported anomalies, suggesting that
LINEST/LOGEST coefficient estimates that are exactly zero in 2003 should
be looked on with some suspicion.
Apparently this is an example of MS being too helpful, as in the
difference between =A29-1 and =(A29-1) with your data.

As previously noted, you can identify the issue by an estimated
coefficient that is exactly zero. In the referenced thread I suggested
some ways to slightly perturb the model in order to avoid this
helpfulness gone wrong (also why you get different results from slightly
different formulations of the problem). In your case, you could also
simply reduce to
=LINEST(D928,A9:C28,,TRUE)

"If the formulas in A9:A29 are replaced by their values" is not entirely
clear. Do you mean Edit|Copy followed by Edit|Paste Special|Values, or
hand entering the values? They will not be the same, your increment
(0.1) has no exact binary representation, and hence must be
approximated. As a result, A19 is -1.38777878078145E-16 instead of
zero, and A29 is slightly less than 1 as shown by (A29-1). You can
reduce the accumulation of approximations by using
=(ROW()-19)/10
in A9:A29

Also, you are working harder than you need to by directly calculating
B9:C29 when you could use
=LINEST(D928,A9:A28^{1,2,3},,TRUE)

Jerry

byundt wrote:

LINEST in Excel 2003 SP1 finds incorrect values for regression coefficients
of cubic polynomials when the values of x are determined by a formula. Excel
97, 2000 and 2002 perform the calculation correctly.

To reproduce this problem:
1) Put the following labels and polynomial coefficients in cells A1:B4
a3 19
a2 23
a1 2
a0 81

2) Put the values of x in cells A9:C29
A9=-1.0
A10=A9+0.1 (copy this formula down)
B9=A9^2 (copy this formula down)
C9=A9^3 (copy this formula down)

3) Put the values of y in cells D929
D9=B\$4+B\$3*A9+B\$2*A9^2+B\$1*A9^3 (copy this formula down)

4) Select cells F9:I13 and array enter the following formula:
=LINEST(D929,A9:C29,TRUE,TRUE)

Note that LINEST in Excel 2003 returns incorrect values for a0 and a2. This
error is consistent even if I change polynomial coefficients in B1:B4. Excel
97, 2000 and 2002 all return the correct values, however.

If the formulas in A9:A29 are replaced by their values, then LINEST works
correctly. Also, if one of the rows of data is deleted (or repeated) then
LINEST works correctly.

Bottom line: I have an acceptable workaround for the time being--but
Microsoft has a bug that needs to be fixed.

#4
March 21st 05, 02:15 PM
 Jerry W. Lewis Posts: n/a

Old news. There are previously reported anomalies, suggesting that
LINEST/LOGEST coefficient estimates that are exactly zero in 2003 should
be looked on with some suspicion.
Apparently this is an example of MS being too helpful, as in the
difference between =A29-1 and =(A29-1) with your data.

As previously noted, you can identify the issue by an estimated
coefficient that is exactly zero. In the referenced thread I suggested
some ways to slightly perturb the model in order to avoid this
helpfulness gone wrong (also why you get different results from slightly
different formulations of the problem). In your case, you could also
simply reduce to
=LINEST(D928,A9:C28,,TRUE)

"If the formulas in A9:A29 are replaced by their values" is not entirely
clear. Do you mean Edit|Copy followed by Edit|Paste Special|Values, or
hand entering the values? They will not be the same, your increment
(0.1) has no exact binary representation, and hence must be
approximated. As a result, A19 is -1.38777878078145E-16 instead of
zero, and A29 is slightly less than 1 as shown by (A29-1). You can
reduce the accumulation of approximations by using
=(ROW()-19)/10
in A9:A29

Also, you are working harder than you need to by directly calculating
B9:C29 when you could use
=LINEST(D928,A9:A28^{1,2,3},,TRUE)

Jerry

byundt wrote:

LINEST in Excel 2003 SP1 finds incorrect values for regression coefficients
of cubic polynomials when the values of x are determined by a formula. Excel
97, 2000 and 2002 perform the calculation correctly.

To reproduce this problem:
1) Put the following labels and polynomial coefficients in cells A1:B4
a3 19
a2 23
a1 2
a0 81

2) Put the values of x in cells A9:C29
A9=-1.0
A10=A9+0.1 (copy this formula down)
B9=A9^2 (copy this formula down)
C9=A9^3 (copy this formula down)

3) Put the values of y in cells D929
D9=B\$4+B\$3*A9+B\$2*A9^2+B\$1*A9^3 (copy this formula down)

4) Select cells F9:I13 and array enter the following formula:
=LINEST(D929,A9:C29,TRUE,TRUE)

Note that LINEST in Excel 2003 returns incorrect values for a0 and a2. This
error is consistent even if I change polynomial coefficients in B1:B4. Excel
97, 2000 and 2002 all return the correct values, however.

If the formulas in A9:A29 are replaced by their values, then LINEST works
correctly. Also, if one of the rows of data is deleted (or repeated) then
LINEST works correctly.

Bottom line: I have an acceptable workaround for the time being--but
Microsoft has a bug that needs to be fixed.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post cntaylor Excel Discussion (Misc queries) 4 April 30th 07 07:08 PM David Purdy Setting up and Configuration of Excel 2 May 3rd 06 09:21 PM maze2009 Excel Discussion (Misc queries) 0 January 20th 05 07:27 PM Pete Carr Excel Discussion (Misc queries) 3 December 27th 04 08:11 AM DHunt Charts and Charting in Excel 0 December 6th 04 08:05 PM

All times are GMT +1. The time now is 01:39 AM.