TREND worksheet function for polynomials

[simal]

The following text can be found in the help for the TREND workshee
function:

You can use TREND for polynomial curve fitting by regressing agains
the same variable raised to different powers. For example, suppos
column A contains y-values and column B contains x-values. You ca
enter x^2 in column C, x^3 in column D, and so on, and then regres
columns B through D against column A.

Can anyone shed any light on how to actually use this information on
worksheet?

I have tried a number of interpretations and cannot get the function t
return a predicted y value for a new input x value with a polynomia
function higher than order 1.

I would be chuffed if someone could send me a worksheet with a
example.

Thanks,
S

 

[Jerry W. Lewis]

For simplicity, we will do a quadratic. Put
=2+B1-B1^2
in A1,
=ROW()
in B1, and
=B1^2
in C1.

Copy/Paste A1:C3 over A2:C5. Select E4:E5 and array enter
(Ctrl+Shift+Enter) the formula
=TREND(A1:A3,B1:C3,B4:C5)
You will see that it correctly predected A4:A5

It is not necessary to explicitly compute the squared terms in the
worksheet. Select F4:F5 and array enter
=TREND(A1:A3,B1:B3^{1,2},B4:C5)
The element B1:B3^{1,2} creates an array in memory only that corresponds
to B1:C3.

Jerry

 

[Jerry W. Lewis]

The last version should have been
=TREND(A1:A3,B1:B3^{1,2},B4:B5^{1,2})
to avoid square term cells for the new x's as well as the old x's

Jerry

For simplicity, we will do a quadratic. Put
=2+B1-B1^2
in A1,
=ROW()
in B1, and
=B1^2
in C1.

Copy/Paste A1:C3 over A2:C5. Select E4:E5 and array enter
(Ctrl+Shift+Enter) the formula
=TREND(A1:A3,B1:C3,B4:C5)
You will see that it correctly predected A4:A5

It is not necessary to explicitly compute the squared terms in the
worksheet. Select F4:F5 and array enter
=TREND(A1:A3,B1:B3^{1,2},B4:C5)
The element B1:B3^{1,2} creates an array in memory only that corresponds
to B1:C3.

Jerry

 

[Guest]

Jerry or anyone could you explain a little more how the trend function is working with the arrays for polynomial fits
The notation or sintax is new to me. I am, I think, an advanced user of Excel and intermediate user of VBA, but I have never used array notation

Could you humer me with a third order example as well

Thank you.

 

[Harlan Grove]

Jerry or anyone could you explain a little more how the trend function is
working with the arrays for polynomial fits. The notation or sintax is new to
me. I am, I think, an advanced user of Excel and intermediate user of VBA,
but I have never used array notation.

Time for reassessment. If you haven't worked with arrays in Excel, you're not an
advanced Excel user.

Could you humer me with a third order example as well.

=TREND(Y,X^{1,2,3},<other_optional_args>)

This form assumes Y and X are single column, multiple row ranges or arrays. The
term X^{1,2,3} then gives a 3-column by multiple row array the first column of
which is just X, the second column is composed of entries which are the squares
of the corresponding entries in X, and the third column is composed of entries
which are the cubes of the corresponding entries in X.