Type mismatch error with linest in vba

Question

I am looping through an array to build a new array that is a subset of the original array's data.

However, when I use the new array as input into LinEst function to generate a polynomial regression, I get a type mismatch error.

Here is a sub I wrote to try to isolate the problem.

Sub GetCoefficients2() Dim i As Integer Dim x_values() As Variant Dim y_values() As Variant Dim x_values_new(8) As Variant Dim y_values_new(8) As Variant Dim coefficients() As Variant

x_values() = Range("B2:B11") y_values() = Range("C2:C11")

For i = 1 To 8

   x_values_new(i) = x_values(i, 1)
   y_values_new(i) = y_values(i, 1)

Next i

coefficients() = Application.LinEst(y_values, Application.Power( _ x_values, Array(1, 2, 3)))

'coefficients() = Application.LinEst(y_values, Application.Power( _ x_values, Array(1, 2, 3)))

End Sub

Note, when I plug the original array into the LinEst function, it generates the coefficients without any errors. I've tried everything I can think of to get the truncated arrays to work, but I'm at my wits end. Do you have a suggestion?

Thank you, James

Answer

James,

I didn't mean to imply they were multiplied - that was a poor attempt to explain something about the power function. The aoriginal array is duplicated and each duplicate is raised to a power - not multiplied. So the single column array is duplicated and each new column raised to one of the powers. The function just seems to need the exponent array to be the opposite orientation to the orignal array - that was the point I was trying to make. Sorry for any confusion on that notation/explanation. I just got carried away.

both of these approaches worked for me. I assume you want to use the x_values_new and y_values_new rather than the original values as you posted. The problems lie in horizontal vice vertical arrays. I have show 3 ways that work for me.

Sub GetCoefficients2() Dim i As Integer Dim x_values() As Variant Dim y_values() As Variant Dim x_values_new(1 To 8) As Variant Dim y_values_new(1 To 8) As Variant Dim coefficients() As Variant Dim v As Variant

x_values() = Range("B2:B11") y_values() = Range("C2:C11")

For i = 1 To 8

  x_values_new(i) = x_values(i, 1)
  y_values_new(i) = y_values(i, 1)

Next i

v = Application.Transpose(Application.Power(Application.Transpose(x_values_new), Array(1, 2, 3)))

coefficients() = Application.LinEst(y_values_new, v)

For i = LBound(coefficients, 1) To UBound(coefficients, 1) Debug.Print i, coefficients(i) Next End Sub

Sub GetCoefficients3() Dim i As Integer Dim x_values() As Variant Dim y_values() As Variant Dim x_values_new(1 To 8) As Variant Dim y_values_new(1 To 8) As Variant Dim coefficients() As Variant Dim v As Variant

x_values() = Range("B2:B11") y_values() = Range("C2:C11")

For i = 1 To 8

  x_values_new(i) = x_values(i, 1)
  y_values_new(i) = y_values(i, 1)

Next i

ReDim v(1 To 3, 1 To 1) v(1, 1) = 1 v(2, 1) = 2 v(3, 1) = 3

coefficients() = Application.LinEst(y_values_new, Application.Power(x_values_new, v))

For i = LBound(coefficients, 1) To UBound(coefficients, 1) Debug.Print i, coefficients(i) Next End Sub

Sub GetCoefficients4() Dim i As Integer Dim x_values() As Variant Dim y_values() As Variant Dim x_values_new(1 To 8, 1 To 1) As Variant Dim y_values_new(1 To 8, 1 To 1) As Variant Dim coefficients() As Variant

x_values() = Range("B2:B11") y_values() = Range("C2:C11")

For i = 1 To 8

  x_values_new(i, 1) = x_values(i, 1)
  y_values_new(i, 1) = y_values(i, 1)

Next i

coefficients() = Application.LinEst(y_values_new, _

 Application.Power(x_values_new, Array(1, 2, 3)))

For i = LBound(coefficients, 1) To UBound(coefficients, 1) Debug.Print i, coefficients(i) Next End Sub

in the case where the x_values_new and y_values_new are one dimensional arrays, these arrays are horizontal rather than vertical. If you make your data horizontal on the worksheet and use your formula you will see that it does not work until you add the two transposes I added.

=LINEST(B17:K17,TRANSPOSE(POWER(TRANSPOSE(B16:K16),{1,2,3})))

this is shown in the revised Sub GetCoefficients2() routine

I moved the data to row 16 and 17 (Y values in 17), columns B to K

=LINEST(B17:K17,Power(B16:K16,{1,2,3})))

doesn't work

An easier conversion might be to make the {1,2,3} a vertical array (it is horizontal as written)

=LINEST(B17:K17,Power(B16:K16,{1,2,3})))

this is show in the Sub GetCoefficients3() routine

In the GetCoefficients4() routine, I made the subarrays two dimensional and vertical (while the power array remains horizontal).

so you have an 8 x 1 times an 1 x 3 results in an 8 x 3 which is required with the y_Values_new being 8 x 1