6th order polynomial interpolation error

[Johnny C]

Hi
I'm trying to do a 6th order polynomial interpolation and it's giving me an error.

This is the formula for the first parameter:

=INDEX(LINEST(OFFSET($C2,0,1,1,'Liquidations actuals'!$DS$2-1),OFFSET($C$1,0,1,1,'Liquidations actuals'!$DS$2-1)^{1,2,3,4,5,6},1),1)

I tried replacing the offsets with D2:AL2 and D1:AL1 and it didn't work. All X and Y values are +ve, and plotting the data with a 6th order trend gives no problems, the equation shows nothing amiss.

Evaluate gives

 =INDEX($D$2:$AL$2,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36}^{1,2,3,4,5,6},1),1)

$D:$AL is a 35x1 array so that should be ok.
Next step in eval gives

=INDEX($D$2:$AL$2,{2,9,64.625,7776,117649,#N/A,#N/A,...#NA},1,1)

which then goes to

=INDEX(#VALUE!,1)

Substituting the OFFSETS with D2:AL2 and D1:AL1 yields the same result.

What's going wrong? Does the data need to be in columns rather than rows?

TIA

 

[Kenneth Hobson]

Maybe you need Transpose()?

Here are a few routines that might or might not help.

Sub Test_YPolyFit()
  Dim v As Variant, i As Integer, j As Double
  Dim rX As Range, rY As Range
  
  Set rX = Range("Z3:z128")
  Set rY = Range("AM3:AM128")
  
  Set rX = Range("AT3:AT189")
  Set rY = Range("BG3:BG189")
  
  For j = 5000 To 20000 Step 5000
    For i = 1 To 16
    '=LINEST(BA3:BA189,AT3:AT189^{1,2,3,4,5,6}) 'i=6 degree fit
      v = PolyFit(rY, rX, i)
      Debug.Print i, j, YPolyFit(j, v)
    Next i
  Next j
End Sub

Sub Test_YPolyFit2()
  Dim v As Variant, i As Integer, j As Double
  Dim rX As Range, rY As Range, d As Double
  
  Set rX = Range("AT3:AT189")
  Set rY = Range("BG3:BG189")
  
  For j = 1000 To 10000 Step 1
    For i = 6 To 6
    '=LINEST(BA3:BA189,AT3:AT189^{1,2,3,4,5,6}) 'i=6 degree fit
      v = PolyFit(rY, rX, i)
      d = YPolyFit(j, v)
      If d <= -2.24291098117828 Then
        Debug.Print j, d  'Find pass for -2.24291098117828 mm rut, where -2.24291098117828+1=rut at 1000 pass
        Debug.Print j - 1, YPolyFit(j - 1, v)
        Exit Sub
      End If
    Next i
  Next j
End Sub

Function YPolyFit(xVal As Double, coeffs As Variant) As Double
  YPolyFit = WorksheetFunction.SeriesSum(xVal, UBound(coeffs) - 1, -1, coeffs)
End Function

Sub Test_PolyFit()
  Dim v As Variant, i As Variant
  v = PolyFit(Range("AM3:AM128"), Range("Z3:z128"), 6)  'Run 1 PT. 6
  v = PolyFit(Range("BA3:BA189"), Range("At3:At189"), 6)  'Run 2 Pt. 6
  For i = LBound(v) To UBound(v)
    Debug.Print v(i)
  Next i
End Sub

Function PolyFit(known_ys As Variant, known_xs As Variant, iDegree As Integer) As Variant
  Dim pf As Variant, i As Integer, a() As Variant
  ReDim a(1 To iDegree)
  
  For i = 1 To iDegree
    a(i) = i
  Next i
  pf = Application.LinEst(known_ys, Application.Power(known_xs, a()))
  
  PolyFit = pf
End Function

 

[Johnny C]

Transpose didn't work unfortunately, it gives a #REF error

 

[RoryA]

I suspect you just need to transpose your powers array and use {1;2;3;4;5;6}

 

[Johnny C]

it does work if I TRANSPOSE both arrays, just means a CSE version is required

 

[RoryA]

Does it not work if you only transpose the array constant?