polynomial regression

[Lucile]

Hello,
I need to do a polynomial regression, 5th order.
I think I am using the right formula: application.linest(x,y^{1,2,3,4,5})
My problem is that {} is not accepted! Invalid character.
I am using excel 2000.

What can I do to fix this problem?
Thanks

 

[Mike Middleton]

Lucile -

(1) I suggest that you think seriously about whether you have data that
should be analyzed using a 5th order polynomial. Use google to search for
"regression overfit" (without the quote marks).

(2) The correct order of the arguments for LINEST is known_y first and
known_x second. It's the known_x data that are raised to the various powers.

(3) Show the VBA code you are using. One approach is to use FormulaArray and
build the arguments as concatenated text strings, in which case the curly
brackets are part of a text string enclosed in double-quote marks.

- Mike Middleton
http://www.MikeMiddleton.com
Excel Add-ins and Books for Data Analysis and Decision Analysis

 

[Dana DeLouis]

VBA doesn't accept { } character, so I can not write this formula!

If x & y are range names for your data, this is one way.
The highest order polynomial coefficient is index 1.

Sub Demo()
Dim M
M = [Linest(y,x^{1,2,3,4,5})]
End Sub

= = =
HTH :>)
Dana DeLouis

 

[Lucile]

Thanks everybody fro your help... But it is not working!

First I know that a 5th order polynomial regression is too much, but it is
what I have to do for now. And with a 3rd order I will have the same
problem...

I tried evaluate... But what I understand is I have to define my range and
not only put a range name...

I tried:

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

x = Range(Cells(2, 1), Cells(lastrow, 1))
y = Range(Cells(2, 7), Cells(lastrow, 7))

M = [linest(y,x^{1,2,3,4,5})]

ActiveCell = M

---> It gives me #NAME?

And:

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

x = Range(Cells(2, 1), Cells(lastrow, 1))
y = Range(Cells(2, 7), Cells(lastrow, 7))

a=application.index(application.linest(y,x^{1,2,3,4,5}),1)

---> It gives invalid character for { }

I really need to find a way to do that!
Thanks!

 

[Lucile]

Ok, I solve my problem!

Application.LinEst(y, Application.Power(x, Array(1, 2, 3, 4, 5)), True, True)

Thaks all

 

[Dana DeLouis]

VBA doesn't accept { } character, so I can not write this formula!

Hi. Glad you got it working. ;>)
Just to add to your library...this is what I meant by a named Range...

Sub Demo()
Dim NRows As Long
Dim M

NRows = Cells(Rows.Count, 1).End(xlUp).Row - 2 + 1

With ActiveWorkbook.Names
.Add "x", Cells(2, 1).Resize(NRows)
.Add "y", Cells(2, 7).Resize(NRows)
End With

M = [Linest(y,x^{1,2,3,4,5})]

'Horizontal
[I2].Resize(1, 6) = M
'or Verticle
[I4].Resize(6) = WorksheetFunction.Transpose(M)
End Sub

= = =
HTH
Dana DeLouis