Interpolation

Hi
I need help on attached workbook interpolation. I am trying to calculate "F"
on the base of variable "S" If there are any questions please inform me.


Thanks a lot

Nick

Can you tell us exactly what you're trying to do, so we don't have to guess?

Hi
Thanks for respond, I am trying to calculate F for different categories of A,B,C,D and E, for different values of S, which can varies from 0 to any number. What I attached it works for S if it is 25%,50%,75%,100% and 125%
but it does not work for any other numbers in between those, so I need to be able to find F let us say 30%, or 65%. I hope that I was able to explain, if you have any questions please contact me.

Thanks again

Shantibala

Here's a function you may be able to adapt:

Public Function Linterp(ByRef Tbl As Range, ByRef dX As Double) As Variant
    ' Linear interpolator / extrapolator
    ' Tbl is a two-column range containing known x, known y, sorted ascending

    Dim i       As Long     ' index to Tbl
    Dim nRow    As Long     ' rows in Tbl
    Dim dXAbv   As Double
    Dim dXBlo   As Double   ' Tbl values flanking dX
    Dim dRF     As Double   ' row fraction

    nRow = Tbl.Rows.Count
    If nRow < 2 Or Tbl.Columns.Count <> 2 Then
        Linterp = "Table must have >= 2 rows, exactly two columns"
        Exit Function    '-------------------------------------------------------->
    End If

    If dX < Tbl(1, 1).Value Then    ' dX < xmin, extrapolate first two entries
        i = 1
    Else
        i = Worksheetfunction.Match(dX, Application.Index(Tbl, 0, 1), 1)
        If dX = Tbl(i, 1).Value Then    ' dX is exact from table
            Linterp = Tbl(i, 2)
            Exit Function    '---------------------------------------------------->
        ElseIf i = nRow Then   ' dX > xmax, extrapolate last two entries
            i = nRow - 1
            'Else
            ' dX lies between two rows, so interpolate entries i, i+1
            ' which is what happens by default
        End If
    End If

    dXAbv = Tbl(i, 1).Value
    dXBlo = Tbl(i + 1, 1).Value
    dRF = (dX - dXAbv) / (dXBlo - dXAbv)    ' row fraction

    Linterp = Tbl(i, 2).Value * (1 - dRF) + Tbl(i + 1, 2).Value * dRF
End Function

And an example:

      A B- -C- ---------D----------
  1   1  1 5.5 =Linterp(A1:B5, 2.3)
  2   2  4                         
  3   3  9                         
  4   4 16                         
  5   5 25

Thanks for your help.
Shantibala