"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.

Regards,
Peter T