On 28/05/2014 21:52, Peter T wrote:
"Martin Brown" wrote in message On
27/05/2014 17:55, Peter T wrote:
"isabelle" wrote in message
hi,

the Regression tool uses the worksheet function LINEST

http://office.microsoft.com/en-us/ex...010342762.aspx

http://office.microsoft.com/en-us/ex...005209155.aspx

x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), False)

isabelle

Indeed the tool uses LinEst but can get more stats like this

x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), True, True)

Returns a 5x2 array of stats as shown on the help page you referred to.

The regression tool returns other stats that LinEst doesn't, though
LinEst
can also return the polyniomial cooefficients of a 'best fit' curved
line.

Be aware that it is only marginally numerically stable if you try to fit
anything more than a quadratic on data with a x axis offset.

By comparison the polynomial fit in the charts is done correctly but
doesn't by default display its answers with enough significant digits.

What still! I just tried a 5 order with 20 'x' values and compared Linest
with the trendline equation (numberformat 11dp) and both were the same to
within 10dp. First tested with a perfect equation and various values for the
coefficients
y = a + bx + cx^2 + dx^3 + ex^4 + fx^5

Then added some random irregularity with y up to +/-5% deviation from the
norm, again Linest and the equation formula returned same coefficients to
within 10dp.

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.

Add an offset of about 10000 to all the x values - this makes the
condition number of the problem much higher for a naive code. It is the
sort of thing that can happen when the x values are dates for example.

When they first released XL2007 they broke the polynomial fit in charts
to make it agree with a well known PC package with the same defect!

--
Regards,
Martin Brown