Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
byundt
 
Posts: n/a
Default 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 D9:D29
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(D9:D29,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   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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.
http://groups.google.com/groups?thre...0no_e-mail.com
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(D9:D28,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(D9:D28,A9:A28^{1,2,3},,TRUE)

Jerry

As for your specific example, your instructions for reproducing are
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 D9:D29
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(D9:D29,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   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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.
http://groups.google.com/groups?thre...0no_e-mail.com
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(D9:D28,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(D9:D28,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 D9:D29
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(D9:D29,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   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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.
http://groups.google.com/groups?thre...0no_e-mail.com
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(D9:D28,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(D9:D28,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 D9:D29
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(D9:D29,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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
quattro converter for Excel 2003 cntaylor Excel Discussion (Misc queries) 4 April 30th 07 07:08 PM
Opening DBF files with Excel 2003 via Windows Explorer ? David Purdy Setting up and Configuration of Excel 2 May 3rd 06 09:21 PM
sharing/using/saving Excel 2002 files in Excel 2003 maze2009 Excel Discussion (Misc queries) 0 January 20th 05 08:27 PM
Excel 2003 back to Excel 2002 Pete Carr Excel Discussion (Misc queries) 3 December 27th 04 09:11 AM
Excel 97 chart opened in Excel 2003 - Source Data problem DHunt Charts and Charting in Excel 0 December 6th 04 09:05 PM


All times are GMT +1. The time now is 11:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"