Linear Interpolation Function - Using for non-linear data

[pcarrollg]

Hi,

I am trying to write a function that will interpolate between values in a table. I have two columns, x and y. I have a value for x, and I want to find the corresponding y based off of the data. I have a function I found that will perform a linear interpolation. However my data is not linear for the entire data set. But for small portions of the table (for example between rows 1 and 2, 2 and 3, 3 and 4... etc.) the data can be assumed to be linear.

What I want is to rewrite the linear interpolation function so that it can calculate based off of a small interval. Write now i am having trouble getting it to work, and keep getting a #Value error. I believe this is a result of the fact that my data is not sorted in terms of descending x values.

Here is the code I am using

Public Function Linterp(Tbl As Range, x As Double) As Variant
     ' linear interpolator / extrapolator
     ' Tbl is a two-column range containing known x, known y, sorted x descending
     
    Dim nRow As Long
    Dim iLo As Long, iHi As Long
     
    nRow = Tbl.Rows.Count
    If nRow < 2 Or Tbl.Columns.Count <> 2 Then
        Linterp = CVErr(xlErrValue)
        Exit Function '-------------------------------------------------------->
    End If
     
    If x > Tbl(1, 1) Then ' x > xmax, extrapolate from first two entries
        iHi = 1
        iLo = 2
    ElseIf x < Tbl(nRow, 1) Then ' x < xmin, extrapolate from last two entries
        iHi = nRow - 1
        iLo = nRow
    Else
        iHi = Application.Match(x, Application.Index(Tbl, 0, 1), 1)
        If Tbl(iHi, 1) = x Then ' x is exact from table
            Linterp = Tbl(iHi, 2)
            Exit Function '---------------------------------------------------->
        Else ' x is between tabulated values, interpolate
            iLo = iHi + 1
        End If
    End If
     
    Linterp = Tbl(iLo, 2) + (Tbl(iHi, 2) - Tbl(iLo, 2)) _
    * (x - Tbl(iLo, 1)) / (Tbl(iHi, 1) - Tbl(iLo, 1))
     
End Function

Here is an example set of data

1.532	160
1.690	133
1.630	142
1.570	152
1.510	164
1.450	177
1.390	193
1.330	211
1.270	233
1.210	259
1.150	291
1.090	330
1.030	379
0.960	453
0.900	539
0.840	656
0.780	819
0.720	1058
0.660	1425
0.600	2027
0.540	3104

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[pcarrollg]

No there is nothing below the data in my range. But I may need to expand the range as well. If I set the range to something I know it will not exceed, such as A1:A50, will I be alright? I just want to make sure that I continue to get #REF errors if the function attempts to interpolate in an area where there is no data.

 

[Andrew Poulsom]

In what way do you get a #REF! error with the FORECAST function?

 

[DiGiMac]

Just wanted to add my thanks to Andrew Poulsom. It took me a while to get my head around the combination of FORECAST, INDEX and MATCH but it was worth the effort.
I have had an additional problem due to the fact that this seems to work only when the values you wish to forecast are within the limits of the values in column A. That's where I got #REF! errors.
I settled for assuming that values outside the A range are linear (hence use FORECAST without INDEX/MATCH) and used a simple IF to check.