Excel Conditional Linear Interpolation

Question

I'm wanting to build a conditional linear interpolation. I have over 31 unique identifiers. Where the range changes based on the identifier it selects. I was thinking I could do a select based on case criteria but that doesn't seem like the most efficient.

Data looks like this. (Where the currency is the identifier)

AED 1 4

AED 2 6

AUD 1 1

AED 3 12

AUD 2 6

AED 4 13

AUD 3 8

---

Below is the original linear interpolation formula.(Without any conditions). Any ideas what would be the best way to tackle this?

 Function Linterp2(rX As Range, rY As Range, x As Double) As Double
     ' linear interpolator / extrapolator
     ' R is a two-column range containing known x, known y
    Dim lR As Long, l1 As Long, l2 As Long
    Dim nR As Long
     'If x = 1.5 Then Stop

    nR = rX.Rows.Count
    If nR < 2 Then Exit Function

    If x < rX(1) Then ' x < xmin, extrapolate
        l1 = 1: l2 = 2: GoTo Interp

    ElseIf x > rX(nR) Then ' x > xmax, extrapolate
        l1 = nR - 1: l2 = nR: GoTo Interp

    Else
         ' a binary search would be better here
        For lR = 1 To nR
            If rX(lR) = x Then ' x is exact from table
                Linterp2 = rY(lR)
                Exit Function

            ElseIf rX(lR) > x Then ' x is between tabulated values, interpolate
                l1 = lR: l2 = lR - 1: GoTo Interp

            End If
        Next
    End If

Interp:
    Linterp2 = rY(l1) _
    + (rY(l2) - rY(l1)) _
    * (x - rX(l1)) _
    / (rX(l2) - rX(l1))

End Function

Answer 1

mmm "you want to build a conditional linear interpolation" and said "Say ID is AED" let me speculate what you really want,

First you have a Function (that do linear interpolate)

Second you have (for easy explain) a table that have 3 columns:

Column 1: ID (value that identify which ranges will use)

Column 2: Number of values have varibale X (range X)

Column 2: Number of values have varibale Y (range Y)

thats to mean if you select ID=AED, Function() will take a range.size for X and range.size for Y (for example taken first row you wrote only take AED)

1: you want "select case" insde your function if this is the case:

*first you select all columns for your ranges (select all column 2 for range X and select all column 3 for range y) and X value.

*then when your function runs; function have to identify Which ID have (you want to know)and resize your ranges X,Y and only take values that have ID like indentify.

So you need change variables in your Function because you need obligatorily a relationship between ID, X-value and y-value for each point. so need a matrix

Function Linterp2(Mtr As Range, x As Double, ID as String) As Double

in this case your range have 3 columns and n rows (need to select all table) then do a "For" where you search for ID in Range

    dim MtrP(0,2)
 for i=1 to mtr.rows.count
    if MtrP(0,0)=nothing then  /*get first value*/
       if Mtr.cells(i,1).value="ID" then
        MtrP(0,0)=Mtr.cells(i,1)
        MtrP(0,1)=Mtr.cells(i,1)
        MtrP(0,2)=Mtr.cells(i,1)
        j=0
       end if
    elseif Mtr.cells(i,1).value="ID" then
        j=j+1
        redim preserve MtrP(j,2)
        MtrP(j,0)=Mtr.cells(i,1)
        MtrP(j,1)=Mtr.cells(i,1)
        MtrP(j,2)=Mtr.cells(i,1)
    end if
next

In this moment your new array with all data you need is MtrP and you can work with it for do your linear interpolate

Answer 2

Check this out, Note that arrays are 0 indexed but ranges are 1 indexed.

Function Linterp3(rX As Range, rY As Range, rID As Range, x As Double, id As String) As Double
    ' Linear interpolator / extrapolator with index criteria
    ' Inputs:
    '        rX - 1 column range of x Values
    '        rY - 1 column range of y Values
    '        rID - 1 column range of index criteria
    '        x - x value criterion
    '        id - index criterion
    
    ' Select the relevant parts of the X,Y ranges based on the id criteria
    Dim rX_selected() As Double, rY_selected() As Double, i As Integer, j As Integer
    j = 0
    
    For i = 1 To rX.Worksheet.UsedRange.Rows.Count
        If rID.Cells(i).Value = id Then

            ReDim Preserve rX_selected(j)
            ReDim Preserve rY_selected(j)

            rX_selected(j) = rX(i).Cells.Value
            rY_selected(j) = rY(i).Cells.Value
            j = j + 1
        End If
    Next
        
    'Linearly interpolate
    Dim lR As Long, l1 As Long, l2 As Long
    Dim nR As Long
    

    nR = j
    If nR < 2 Then Exit Function

    If x < rX_selected(0) Then ' x < xmin, extrapolate
        l1 = 1: l2 = 2: GoTo Interp

    ElseIf x > rX_selected(nR - 1) Then ' x > xmax, extrapolate
        l1 = nR - 1: l2 = nR: GoTo Interp

    Else
         ' a binary search would be better here
        For lR = 1 To nR - 1
            If rX_selected(lR) = x Then ' x is exact from table
                Linterp3 = rY_selected(lR)
                Exit Function

            ElseIf rX_selected(lR) > x Then ' x is between tabulated values, interpolate
                l1 = lR: l2 = lR - 1: GoTo Interp

            End If
        Next
    End If

Interp:
    Linterp3 = rY_selected(l1) _
    + (rY_selected(l2) - rY_selected(l1)) _
    * (x - rX_selected(l1)) _
    / (rY_selected(l2) - rX_selected(l1))
        
End Function