Q:

VBA Linest function to interpolate a value

Hi trying to use the Worksheet function LinEst, to work as an estimator for a given X. Firstly found a function that would give me a list of numbers 1 to n to use as powers. Then tried to incorporate into the function. But I am sure I have made multiple errors. Ultimately, I want to enter an x range a y range, the polynomial order of my linear estimate and an x to have an estimated outcome.

Option Explicit

Public Function ReturnArrayAtoB(ByVal M As Long, ByVal N As Long) As Variant

    Dim lngCounter  As Long
    Dim arrReturn   As Variant

    ReDim arrReturn(N - M)

    For lngCounter = 0 To N - M
        arrReturn(lngCounter) = M + lngCounter
    Next lngCounter

    ReturnArrayAtoB = arrReturn

End Function

Function PolyFit(rX As Range, rY As Range, Xinput, Optional PolyOrder = 3)
Dim varLinEst As Variant
Dim arrReturn As Variant
Dim i As Integer
Dim a As Long

arrReturn = ReturnArrayAtoB(1, PolyOrder)

varLinEst = WorksheetFunction.LinEst(rY, ((rX) ^ (arrReturn)), True, False)

a = 0
For i = 1 To PolyOrder
    a = Xinput ^ varLinEst(i) + a
Next i
a = a + varLinEst(0)

    PolyFit = a

PolyFit = varLinEst

End Function

A:

you need to late bind the formula and use Application.Power
there were some other logic issues that I fixed.
Public Function ReturnArrayAtoB(ByVal M As Long, ByVal N As Long) As Variant
    Dim arrReturn   As Variant
    ReDim arrReturn(N - M)

    Dim lngCounter  As Long
    For lngCounter = 0 To N - M
        arrReturn(lngCounter) = M + lngCounter
    Next lngCounter

    ReturnArrayAtoB = arrReturn

End Function

Function PolyFit(rX As Range, rY As Range, Xinput As Double, Optional PolyOrder = 3)
    Dim arrReturn As Variant
    arrReturn = ReturnArrayAtoB(1, PolyOrder)

    Dim varLinEst As Variant
    varLinEst = Application.LinEst(rY, Application.Power(rX, arrReturn))

    Dim a As Double
    a = 0#

    Dim i As Long
    For i = 1 To PolyOrder + 1
        a = (Xinput ^ (PolyOrder + 1 - i)) * varLinEst(i) + a
    Next i

    PolyFit = a
End Function

And just because, the following formula does the same, if one has Sequence():
=SUMPRODUCT(INDEX(LINEST(B1:B8,(A1:A8^SEQUENCE(,E2))),SEQUENCE(E2+1)),E1^(SEQUENCE(E2+1,,E2,-1)))

