I have an array function that I have used to make give me the coefficiencts of the Least Squares polynomial regression. The function works fine to solve them, but when I do things like insert or delete rows or columns, the function breaks down and puts the first value of the array in all of the cells of the array. I try to then use Application.CalculateFull, but that does not work until I highlight one instance of the formula, press F2, then Ctrl-Shift-Enter to update the formula. Then I can use Application.CalculateFull to update the rest. Any advice?
Here is the Code:
Here is the Code:
Code:
Function PolyReg(xvals As Range, yvals As Range, RegOrder As Integer)
ReDim xVect(0 To RegOrder * 2)
ReDim yxVect(0 To RegOrder)
ReDim LHMatrix(0 To RegOrder, 0 To RegOrder)
Dim ListLen As Integer
ListLen = Application.WorksheetFunction.Max(xvals.Rows.Count, xvals.Columns.Count)
For i = 1 To ListLen
xVect(0) = xVect(0) + 1
For j = 1 To RegOrder * 2
xVect(j) = xVect(j) + xvals(i) ^ j
Next j
yxVect(0) = yxVect(0) + yvals(i)
For j = 1 To RegOrder
yxVect(j) = yxVect(j) + yvals(i) * xvals(i) ^ j
Next j
Next i
For i = 0 To RegOrder
For j = 0 To RegOrder
LHMatrix(i, j) = xVect(RegOrder - j + i)
Next j
Next i
yxVect = Application.WorksheetFunction.Transpose(yxVect)
LHMatrix = Application.WorksheetFunction.MInverse(LHMatrix)
yxVect = Application.WorksheetFunction.MMult(LHMatrix, yxVect)
If Selection.Rows.Count > 1 Then
PolyReg = yxVect
Else
PolyReg = Application.WorksheetFunction.Transpose(yxVect)
End If
End Function