###### 编程技术网

 用户名 Email 自动登录 找回密码 密码 立即注册

# LinEst用于多项式函数vba:LinEst for polynomial function vba

alekzg 集合 2022-5-11 10:42 20人围观

LinEst用于多项式函数vba的处理方法

I am trying to calculate a polynomial regression using vba. First, I tried y=x^2+b:

``OUTPUT = WorksheetFunction.Application.LinEst (A,Application.Power(C,2),True,True) ``

where A and C are arrays and the `OUTPUT` is good. I can read `r2` from `OUTPUT` using `Application.Index(OUTPUT,3)`

However, when I want to try `y=x+x^2+b` by adding Array to the argument of Array:

``OUTPUT = WorksheetFunction.Application.LinEst (A,Application.Power(C,Array(1,2)),True,True) ``

Any solution? What am I doing wrong?

Solution: R_SQUARE = Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Application.Transponse(Array(1, 2))), True, True), 3,1)

### 问题解答

``Sub LinEst() Dim yVal As Range, xVal As Range Set yVal = Range("C5:C14") Set xVal = Range("B5:B14") 'You tried following formula which gives incorrect results for polynomial order 2 Range("B17") = Application.Index(WorksheetFunction.LinEst(yVal, _ Application.Power(xVal, 2), True, True), 3) 'For linear Range("B18") = Application.Index(WorksheetFunction.LinEst(yVal, _ xVal, True, True), 3) 'For polynomial order 2 Range("B19") = Application.Index(WorksheetFunction.LinEst(yVal, _ Application.Power(xVal, Array(1, 2)), True, True), 3) 'For polynomial order 3 Range("B20") = Application.Index(WorksheetFunction.LinEst(yVal, _ Application.Power(xVal, Array(1, 2, 3)), True, True), 3) End Sub ``

I tried with `=INDEX(LINEST({3,2,5,7,4,2,1,-2,-5,-1},{0,1,2,3,4,5,6,7,8,9},TRUE,TRUE),3)` in worksheet. But in VBA i couldn't assign values to an array as double. But when tried @Domenic 's suggestion in comments below without data type it worked.

``Sub LinEst() 'Dim xVal(1 To 10) As Double, yVal(1 To 10) As Double 'This fails xVal = Application.Transpose(Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)) yVal = Application.Transpose(Array(3, 2, 5, 7, 4, 2, 1, -2, -5, -1)) 'For polynomial order 3 Range("B20") = Application.Index(WorksheetFunction.LinEst(yVal, _ Application.Power(xVal, Array(1, 2, 3)), True, True), 3) End Sub ``