Home 
Search 
Today's Posts 
#1




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 beingbut Microsoft has a bug that needs to be fixed. 
#2




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_email.com Apparently this is an example of MS being too helpful, as in the difference between =A291 and =(A291) 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 EditCopy followed by EditPaste SpecialValues, 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.38777878078145E16 instead of zero, and A29 is slightly less than 1 as shown by (A291). 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 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 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 beingbut Microsoft has a bug that needs to be fixed. 
#3




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_email.com Apparently this is an example of MS being too helpful, as in the difference between =A291 and =(A291) 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 EditCopy followed by EditPaste SpecialValues, 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.38777878078145E16 instead of zero, and A29 is slightly less than 1 as shown by (A291). 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 beingbut Microsoft has a bug that needs to be fixed. 
#4




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_email.com Apparently this is an example of MS being too helpful, as in the difference between =A291 and =(A291) 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 EditCopy followed by EditPaste SpecialValues, 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.38777878078145E16 instead of zero, and A29 is slightly less than 1 as shown by (A291). 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 beingbut Microsoft has a bug that needs to be fixed. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
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 