Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
quattro converter for Excel 2003 | Excel Discussion (Misc queries) | |||
Opening DBF files with Excel 2003 via Windows Explorer ? | Setting up and Configuration of Excel | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 back to Excel 2002 | Excel Discussion (Misc queries) | |||
Excel 97 chart opened in Excel 2003 - Source Data problem | Charts and Charting in Excel |