Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 182
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 837
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 182
Default 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
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
Excel Range Value issue (Excel 97 Vs Excel 2003) Keeno Excel Discussion (Misc queries) 2 June 13th 05 02:01 PM
Importing xml Data into Excel 2002 CMichaelAPCC Excel Discussion (Misc queries) 0 June 9th 05 03:14 PM
how do I enable "import text file" excel 2002? jw_schmid Excel Discussion (Misc queries) 2 February 9th 05 11:39 PM
Excel 2003 back to Excel 2002 Pete Carr Excel Discussion (Misc queries) 3 December 27th 04 09:11 AM
Macro in Excel 2002 to save a workbook to a FTP location Lloyd Excel Discussion (Misc queries) 0 December 21st 04 03:49 PM


All times are GMT +1. The time now is 02:25 PM.

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"