LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #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.

 
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 07:27 PM
Excel 2003 back to Excel 2002 Pete Carr Excel Discussion (Misc queries) 3 December 27th 04 08:11 AM
Excel 97 chart opened in Excel 2003 - Source Data problem DHunt Charts and Charting in Excel 0 December 6th 04 08:05 PM


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

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

About Us

"It's about Microsoft Excel"