Multivariate Polynomial LINEST with Macro

sfrey

New Member
Joined
Aug 23, 2016
Messages
2
I've dug and dug, but I can't find out to use the LINEST function to calculate the polynomial fit for multivariate data in a macro independent of the worksheet. To be clear, I have found out how to do multivariate-polynomial fits in the worksheet, I just haven't figured out how to do this in a macro. To keep it simple, let's just keep it to two variables and second or third degree polynomials.The most that I can do up to this point in a macro is a polynomial fit for one variable, but I'm perplexed as to how I may expand this to multiple variables. Thanks so much for you help.

The macro below works for the one variable, second degree in which my y-data is in column A and my x-data is in column B

Sub MultivariablePolynomialLINEST()
Dim rY As Range, rX As Range
Dim vArr() As Variant


Set rX = Range("B1:B7")
Set rY = Range("A1:A7")

vArr = Application.LinEst(rY, Application.Power(rX, Array(1, 2)))




End Sub

If I wanted to expand this to two variables up to the second degree, I enter the following as an array in the worksheet.

Assuming y data in column A, x1 data in column B, x2 data in column C.
=LINEST(A1:A7,B1:B7^{1,2,0,0***C1:C7^{0,0,1,2**,True,True)

When I carry this format over to the macro I get a type mismatch error using the following code.

Sub MultivariablePolynomialLINEST()
Dim rY As Range, rX As Range, rZ As Range
Dim vArr() As Variant


Set rX = Range("B1:B7")
Set rY = Range("A1:A7")
Set rZ = Range("C1:C7")
vArr = Application.LinEst(rY, Application.Power(rX, Array(1, 2, 0, 0)) * Application.Power(rZ, Array(0, 0, 1, 2)))




End Sub





Thanks again
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

sfrey

New Member
Joined
Aug 23, 2016
Messages
2
Assuming y data in column A, x1 data in column B, x2 data in column C.
=LINEST(A1:A7,B1:B7^{1,2,0,0***C1:C7^{0,0,1,2**,True,True)
This line should have read....
=LINEST(A1:A7,B1:B7^{1,2,0,0}*C1:C7^{0,0,1,2},True,True)

I don't know what threw in the extra asterisks when I pasted it.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,516
Messages
5,469,097
Members
406,635
Latest member
jfhunter64

This Week's Hot Topics

Top