title: vba excel inverse cubic spline interpolation
body: <p>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.</p>
  
  <pre><code>   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
  </code></pre>
  
  <p>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.</p>
  
  <p>What I need to do is to calculate the X parameter where my Y REALLY is = 0.</p>
  
  <p>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?</p>
  
  <pre><code>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 &lt;&gt; 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) &gt; x Then
          khi = k
      Else
          klo = k
      End If
  
      k = khi - klo
  Loop While k &gt; 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
  </code></pre>