Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Precision in Excel 2002
Hello, All!
I have read complaints about lack of agreement in the coefficients of polynomials obtained by fitting trendlines on the graph and those obtained from LINEST. I had occasion to do some experiments yesterday using a third order polynomial with Excel 2002 and, as far as I can tell, at the same number of decimal places the coefficient results are identical. The data used had random errors and was not from an exact curve. Can anyone tell me which versions of Excel can be relied on or else what problems I should look out for? James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Precision in Excel 2002
The formula used by LINEST prior to 2003 was mathematically correct, but
numerically inferior to that used by the chart trendline. Since it was mathematically correct, you would see no difference unless the problem was very ill-conditioned [=MMULTIPLY(TRANSPOSE(x_mat),x_mat) nearly singular]. Typically this happened when people tried to fit too many polynomial terms over too narrow a range of observations, as in http://groups.google.com/group/micro...9a2bb33e6cdbb8 LINEST in 2003 introduced an algorithm that is far better numerically than earlier versions (essentially as good as the chart trendline). However there is a bug in the implementation of that algorithm that can produce one or more coefficients that are exactly zero when they should be far from zero, even though earlier versions would have experienced no numerical difficulties on these particular data sets http://groups.google.com/group/micro...98be08e90c3cfa The publically available 2007 beta appears to have fixed the problem in the new LINEST algorithm. Bottom line: for most simple stuff it won't matter whether you use LINEST or the chart trendline. However, for any version prior to 2003, if there is a discrepancy between the chart trendline and LINEST, then don't believe LINEST. (This assumes that you are using an "XY (Scatter)" chart with numeric x-data provided--otherwise the chart trendline may not mean what you expect it too.) Similarly, if LINEST in 2003 (PC) or 2004 (Mac) reports any coefficient as exactly zero, don't believe it without indepenent verification (such as the chart trendline). Jerry "James Silverton" wrote: Hello, All! I have read complaints about lack of agreement in the coefficients of polynomials obtained by fitting trendlines on the graph and those obtained from LINEST. I had occasion to do some experiments yesterday using a third order polynomial with Excel 2002 and, as far as I can tell, at the same number of decimal places the coefficient results are identical. The data used had random errors and was not from an exact curve. Can anyone tell me which versions of Excel can be relied on or else what problems I should look out for? James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Precision in Excel 2002
Hello, Jerry!
You wrote on Mon, 14 Aug 2006 09:28:01 -0700: JWL LINEST in 2003 introduced an algorithm that is far better JWL numerically than earlier versions (essentially as good as JWL the chart trendline). However there is a bug in the JWL implementation of that algorithm that can produce one or JWL more coefficients that are exactly zero when they should JWL be far from zero, even though earlier versions would have JWL experienced no numerical difficulties on these particular JWL data JWL setshttp://groups.google.com/group/microsoft.public.excel. JWL programming/browse_frm/thread/aaa78a91ec42fd4b/98be08e90c3 JWL cfa#98be08e90c3cfa JWL The publically available 2007 beta appears to have fixed JWL the problem in the new LINEST algorithm. JWL Bottom line: for most simple stuff it won't matter whether you JWL use LINEST or the chart trendline. However, for any JWL version prior to 2003, if there is a discrepancy between JWL the chart trendline and LINEST, then don't believe LINEST. JWL (This assumes that you are using an "XY (Scatter)" chart JWL with numeric x-data provided--otherwise the chart JWL trendline may not mean what you expect it too.) JWL Similarly, if LINEST in 2003 (PC) or 2004 (Mac) reports JWL any coefficient as exactly zero, don't believe it without JWL indepenent verification (such as the chart trendline). JWL Jerry JWL "James Silverton" wrote: ?? Hello, All! ?? ?? I have read complaints about lack of agreement in the ?? coefficients of polynomials obtained by fitting trendlines ?? on the graph and those obtained from LINEST. I had ?? occasion to do some experiments yesterday using a third ?? order polynomial with Excel 2002 and, as far as I can ?? tell, at the same number of decimal places the coefficient ?? results are identical. The data used had random errors and ?? was not from an exact curve. ?? ?? Can anyone tell me which versions of Excel can be relied ?? on or else what problems I should look out for? Thanks very much! I had assumed that the trendline might be less accurate than LINEST. Again, I live and learn! James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Importing xml Data into Excel 2002 | Excel Discussion (Misc queries) | |||
how do I enable "import text file" excel 2002? | Excel Discussion (Misc queries) | |||
Excel 2003 back to Excel 2002 | Excel Discussion (Misc queries) | |||
Macro in Excel 2002 to save a workbook to a FTP location | Excel Discussion (Misc queries) |