"Martin Brown" wrote in message
On 28/05/2014 22:29, Peter T wrote:
"Peter T" wrote in message


I know that's not a 'stressful' test but could you illustrate when
Linest
becomes unreliable and/or returns different coeff's to a high precision
chart formula.

Hmm, with a bit more testing I am getting discrepancies between Linest
and
the chart formula, but it's the chart formula that's wrong (particularly
with the first x is not increasing from 1), Linest still returning same
coef's as the original formula (with no artificial deviation).

I was stupid, I should have used an XY chart if Xs are not 1,2,3 etc, then
Linest & the chart formula return same. Otherwise the chart formula assumes
X increments in intervals of 1 starting from 1.

I haven't tested it very recently but normally the chart formula is done
right and LinEst is far too crude to solve it correctly.

You can fix the fault in LinEst by rescaling your problem to control the
condition number so that the x-axis runs from -1 to 1.

The problem arises when people fit a trend against date/time values.
(I don't approve of them fitting high order polynomials to begin with but
if they do it is incumbent on the code to return a true best fit!)

Date values as Xs counting from year 1901 are surely unrealistic if using as
Xs in a high order polynomial, unless the first date is say early in 1901.
However Time values don't seem to be a problem (should be in an XY chart).

Sample data that shows it should be in the thread

https://groups.google.com/forum/#!search/excel$20polynomial$20fit$20/microsoft.public.excel.worksheet.functions/QWVh_4C2gkA/cyYk3ah6n_kJ

I tested with his sample data and got identical results with Linest and the
chart formula (using an xy chart), also the same as his chart formula.
However his Linest results were obviously different - then I noticed he was
using Excel 2000. So I dug up my 2000 and reproduced his Linest results. IOW
Linest appears to have been fixed in 2007 (if not 2003?).

I did say "what still" in my earlier reply, just noticed that thread is also
dated 2000, apart from lets say unreasonable data do you still think Linest
is unreliable?

Regards,
Peter T