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>

 

[Andrew Poulsom]

Welcome to MrExcel.

If your data is sorted ascending by column A you can use a formula like:

=FORECAST(C1,INDEX(B1:B21,MATCH(C1,A1:A21)):INDEX(B1:B21,MATCH(C1,A1:A21)+1),INDEX(A1:A21,MATCH(C1,A1:A21)):INDEX(A1:A21,MATCH(C1,A1:A21)+1))

 

[shg]

If you sort the data by x, that routine will work (it's an older version of code I wrote some years ago).

In the alternative, since your data is strictly monotone, you could do it like this:

       --A-- -B-- C --D-- --E--
   1     x    y       x     y  
   2   1.532  160   1.000 410.7
   3   1.690  133              
   4   1.630  142              
   5   1.570  152              
   6   1.510  164              
   7   1.450  177              
   8   1.390  193              
   9   1.330  211              
  10   1.270  233              
  11   1.210  259              
  12   1.150  291              
  13   1.090  330              
  14   1.030  379              
  15   0.960  453              
  16   0.900  539              
  17   0.840  656              
  18   0.780  819              
  19   0.720 1058              
  20   0.660 1425              
  21   0.600 2027              
  22   0.540 3104

The formula in E2 is

=PERCENTILE($B$2:$B$22, 1 - PERCENTRANK($A$2:$A$22, D2, 6))

 

[pcarrollg]

Andrew,

That function works well for what I'm looking for. Could you just explain what it's actually doing? What is the purpose of the INDEX and MATCH functions used?

Also, I realized that there was an error in my data. The first value in the x column should be 1.75, so the x column is descending. I'm trying to write a Macro that will work for a large set of data. I need to interpolate this data between x and y. However in some cases, I will need to interpolate a y value based on a given x value, while in other cases I will need to interpolate an x value for a given y value. Do you think an if then type loop would be able to work for this?

Do you guys have any suggestions? I really appreciate the help so far.

Thanks

 

[Andrew Poulsom]

INDEX is being used to return a reference - notice the colon separating the two calls to the function. The first reference is to the cell in the indexed column whose row in column A contains the largest value that is less than or equal to C1. The second reference is the cell below the first reference. The syntax for FORECAST is:

FORECAST(x, known_y's, known_x's)

so just adjust the column references for arguments 2 and 3 to suit the value you want to predict.

 

[pcarrollg]

I'm having some trouble getting the FORECAST function to work, and keep getting a #REF error. Going through the calculation steps, I believe this may be because I have my data sorted in descending x, and it is getting the error on the MATCH function.

I believe I need to change the match_type to -1. I'll try that and see if it works.

 

[pcarrollg]

Actually looking again at the Forecast Function you gave previously

=FORECAST(C1,INDEX(B1:B21,MATCH(C1,A1:A21)):INDEX(B1:B21,MATCH(C1,A1:A21)+1),INDEX(A1:A21,MATCH(C1,A1:A21)):INDEX(A1:A21 ,MATCH(C1,A1:A21)+1))

Will I have to change the +1 in the index column? So for example my code now looks like this.

=FORECAST(C1,INDEX(B1:B21,MATCH(C1,A1:A21,-1)):INDEX(B1:B21,MATCH(C1,A1:A21,-1)+1),INDEX(A1:A21,MATCH(C1,A1:A21,-1)):INDEX(A1:A21 ,MATCH(C1,A1:A21,-1)+1))

Will this work for descending x values?

Thanks again

 

[Andrew Poulsom]

With a third argument of -1 MATCH finds the smallest value that is greater than or equal to lookup_value. So you still need the cell below (+1).

 

[pcarrollg]

Is there a way to recognize the last row of data that is used in my Range inside the Forecast function?

For the data I am interpolating, I need to be able to change the data used periodically. Is there a way I have have the R1C1 notation recognize the last row of data. For example right now the Forecast function above is using the range A1:A21 and B1:B21. However the number of rows may change to A1:A18 and B1:B18. Is there a way to have the code automatically recognize this?

 

[Andrew Poulsom]

Is there anything below the last row containing data in your range? Incidentally, I don't think it matters if your data shrinks.