Find y values in a data series (MS Excel chart) corresponding to x values along x axi

[Vienna Night]

I have a MS Excel chart with 3 data series. I would like to write a procedure that will loop through the x axis from values 1 to 100 and for each of the x values it will look up the corresponding y values from the 3 data series in my chart.

Bear in mind the x axis values to loop through may not necessarily correspond to the x values from the data series points. In other words, there may be a data series point with a y value of 6 and x value of 5.5. I'm interested in obtaining the corresponding y value for x = 5 which is not not an existing point in the data series; however, one can easily find it when moving the cursor along the curve. My goal is to determine it in VB.

Is there a method or a routine that does that?

Best,

ViennaNight

[SamOscarBrown]

Absolutely...it's called coding. Sorry, couldn't resist. This sounds a lot like a homework assignment. SO, what have YOU already written (in code, that is), attempting this challenge?

[DEXWERX]

an algorithm even? the easiest would be linear interpolation.

[Elroy]

Or cubic spline interpolation, which is what's typically used in these situations.



The "True" and "Cubic Spline" in that example are near identical, whereas the linear can have large inaccuracies.

I've got a cubic spline function. If this is what you think you need, let me know in a post here, and I'll post it.

Good Luck,
Elroy

[Elroy]

Here you go. I had it rather deeply integrated into my primary project, but I tore it out and cleaned it up. The following is a class I named clsCubicSpline (saved as CubicSpline.cls):

Option Explicit
'
Private Type CubicSplineDataType
    xData() As Double
    yData() As Double
    SecondDeriv() As Double
End Type
'
Private TheData As CubicSplineDataType
'

Friend Function SetData(xData() As Double, yData() As Double)
    '
    ' This MUST be called before Value is called.
    ' Once this is called, you can call Value to get cubic spline interpolated values.
    ' The LBound and UBound don't matter, but they must be the same for both xData and yData.
    ' xData need not be interval data (equal distances), but it must be ordinal data (continually increasing).
    ' Errors will occur if either xData or yData isn't dimmed and/or if they're not the same size.
    '
    Dim i As Long
    Dim k As Long
    Dim p As Double
    Dim qn As Double
    Dim sig As Double
    Dim un As Double
    Dim u() As Double
    '
    If LBound(xData) <> LBound(yData) Then Error 12345&                 ' This is a criteria.
    If UBound(xData) <> UBound(yData) Then Error 12345&                 ' This is a criteria.
    '
    ReDim u(LBound(xData) To UBound(xData) - 1&)
    TheData.xData = xData
    TheData.yData = yData
    ReDim TheData.SecondDeriv(LBound(xData) To UBound(xData)) As Double ' These are the 2nd derivative values.
    '
    TheData.SecondDeriv(LBound(xData)) = 0#
    u(LBound(xData)) = 0#
    '
    For i = LBound(xData) + 1 To UBound(xData) - 1
        sig = (xData(i) - xData(i - 1&)) / (xData(i + 1&) - xData(i - 1&))
        p = sig * TheData.SecondDeriv(i - 1&) + 2#
        TheData.SecondDeriv(i) = (sig - 1#) / p
        u(i) = (yData(i + 1&) - yData(i)) / _
               (xData(i + 1&) - xData(i)) - _
               (yData(i) - yData(i - 1&)) / _
               (xData(i) - xData(i - 1&))
        u(i) = (6# * u(i) / (xData(i + 1&) - xData(i - 1&)) - sig * u(i - 1&)) / p
    Next i
    '
    qn = 0#
    un = 0#
    '
    TheData.SecondDeriv(UBound(xData)) = (un - qn * u(UBound(xData) - 1&)) / (qn * TheData.SecondDeriv(UBound(xData) - 1&) + 1#)
    '
    For k = UBound(xData) - 1& To LBound(xData) Step -1&
        TheData.SecondDeriv(k) = TheData.SecondDeriv(k) * TheData.SecondDeriv(k + 1&) + u(k)
    Next k
End Function

Friend Function Value(xValue As Double) As Double
    '
    ' Once SetData is called, this can be used to return Y values for a given X value based on cubic spline interpolation.
    ' Ideally, the input X values should be within the range of the original X values, or results will be unpredictable.
    '
    Dim k As Long
    Dim kLo As Long
    Dim kHi As Long
    Dim Diff As Double
    Dim b As Double
    Dim a As Double
    '
    kLo = LBound(TheData.xData)
    kHi = UBound(TheData.xData)
    Do
        k = kHi - kLo + LBound(TheData.xData) - 1&
        If TheData.xData(k) > xValue Then
            kHi = k
        Else
            kLo = k
        End If
        k = kHi - kLo + LBound(TheData.xData) - 1&
    Loop While k > LBound(TheData.xData)
    '
    Diff = TheData.xData(kHi) - TheData.xData(kLo)
    a = (TheData.xData(kHi) - xValue) / Diff
    b = (xValue - TheData.xData(kLo)) / Diff
    '
    Value = a * TheData.yData(kLo) + _
            b * TheData.yData(kHi) + _
            ((a * a * a - a) * TheData.SecondDeriv(kLo) + _
             (b * b * b - b) * TheData.SecondDeriv(kHi)) * (Diff * Diff) / 6#
End Function

Friend Sub ClearData()
    '
    ' This just clears anything from TheData.
    '
    Erase TheData.xData
    Erase TheData.yData
    Erase TheData.SecondDeriv
End Sub

I also attached a little "test" project for it which illustrates how you might use it. If you understand interpolation, after reading the brief comments in the code, the rest should be fairly straightforward.

Enjoy,
Elroy

EDIT1: And Vienna, I apologize if I gave you instructions for a rocket ship when you were trying to understand how to do jumping jacks.