vba excel inverse cubic spline interpolation

Question

I recently came across a problem with a dataset I have, since its "resolution" is way too small. The dataset is charaterized to have a maximum (0) which is not really its maximum, since all the numbers are normalized to the higher number found within its "resolution", which is basically the "1 by 1 unit" of the "X" column. The Y values are always similar to a parabolic curve.

   Y       X
-34,32   -93
-16,56   -92
-10,04   -91
-6,03    -90
-3,34    -89
-1,56    -88
-0,47    -87
 0       -86
-0,10    -85
-0,78    -84
-2,11    -83
-4,20    -82
-7,36    -81
-12,31   -80
-22,03   -79
-25,17   -78

Now, I know for sure that the 0 should be somewhere between 0 and -0,10 or between 0 and -0,47. A linear interpolation would give me too much error, so I guess I would use a Cubic Spline.

What I need to do is to calculate the X parameter where my Y REALLY is = 0.

Too bad I couldn't use what I found on Internet because every function I try calculates Y from a specified X (below an example of code). Can anyone help?

Function SpLine(PeriodCol As Range, RateCol As Range, x As Range)

Dim Period_Count As Integer
Dim Rate_Count As Integer
Dim c As Integer
Dim n As Integer
Dim i, k As Integer
Dim p, qn, sig, un As Single
Dim klo, khi As Integer
Dim h, b, a As Single

' shows the calculation of a cubic spline interpolated value given known values for fixed periods

Period_Count = PeriodCol.Rows.Count
Rate_Count = RateCol.Rows.Count

If Period_Count <> Rate_Count Then
    SpLine = "Error: Range count dos not match"
    GoTo endnow
End If

ReDim xin(Period_Count) As Single
ReDim yin(Period_Count) As Single

For c = 1 To Period_Count
    xin(c) = PeriodCol(c)
    yin(c) = RateCol(c)
Next c

ReDim u(Period_Count - 1) As Single
ReDim yt(Period_Count) As Single
n = Period_Count
yt(1) = 0
u(1) = 0

For i = 2 To n - 1
    sig = (xin(i) - xin(i - 1)) / (xin(i + 1) - xin(i - 1))
    p = sig * yt(i - 1) + 2
    yt(i) = (sig - 1) / p
    u(i) = (yin(i + 1) - yin(i)) / (xin(i + 1) - xin(i)) - (yin(i) - yin(i - 1)) / (xin(i) - xin(i - 1))
    u(i) = (6 * u(i) / (xin(i + 1) - xin(i - 1)) - sig * u(i - 1)) / p
Next i

qn = 0
un = 0
yt(n) = (un - qn * u(n - 1)) / (qn * yt(n - 1) + 1)

For k = n - 1 To 1 Step -1
    yt(k) = yt(k) * yt(k + 1) + u(k)
Next k

klo = 1
khi = n

Do
    k = khi - klo
    If xin(k) > x Then
        khi = k
    Else
        klo = k
    End If

    k = khi - klo
Loop While k > 1

h = xin(khi) - xin(klo)
a = (xin(khi) - x) / h
b = (x - xin(klo)) / h
SpLine = a * yin(klo) + b * yin(khi) + ((a ^ 3 - a) * yt(klo) + (b ^ 3 - b) * yt(khi)) * (h ^ 2) / 6

endnow:

End Function

Noldor130884 Noldor130884 · Accepted Answer · 2013-10-25T12:44:02

Well since no one answered, I supposed I needed to find some other way.

As my values are similar to the y of a parabolic curve, I found the coefficient of a 3rd degree polynom, which approximates this curve.

This is made by filling a formula in an array of cells 1x4:

worksheet.MyRange1x4.formulaarray = "=LINEST(" & MyX & "," & MyY & "^{1,2,3})"

Where MyX and MyY are again arrays with the same dimention (1 x n)

The results are the "a", "b", "c" and "d" from the polynom ax^3+bx^2+cx+d

Now, if I wanted to find the X of my maximum I just had to solve a delta, which is very similar to the one of the second degree equations: Delta = (-b (+/-) SQRT(b^2 - 3ac))/3a

Forcing the Delta to be 0, I obtained 2 possible X values (because of the +/-SQRT), of which one was definitely in the range where all my nearly maximum values were, and the other was totally wrong with this range.

Selecting the correct one made me finally find my X out.

vba excel inverse cubic spline interpolation

1 Answers