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