You are on the Home/Excel/Tutorials/Interpolation page
Google
Web This Site

Interpolation

Introduction
The basics
Applying the basics to a data set in Excel
Alternative formulations
A VBA function
Two dimensional interpolation

Recent Comments

 

Bill K on Dec 29, 2011:

Your two dimensional interpolation function works great.  You obviously put a lot of work into it.  Well done.

 

DaleW on May 19, 2011:

Nicely done, as usual.

 

 

Introduction

There are a few different types of interpolation techniques.  One relies on creating a curve of best fit taking into account the entire data set.  This is addressed in the topic Trendline Coefficients and Regression Analysis.  Another technique uses piecewise linear interpolation.  As the name implies it fits a straight line to each consecutive pair of data points and uses the appropriate straight line for interpolation.  It is this technique that is discussed in this section.  Other techniques rely on smooth curves that pass through all the data points.  These typically are Cubic Spline or Bezier curves and are outside the scope of this section.

An example of piecewise linear interpolation:

Figure 1
Figure 2 --
Each consecutive pair of points is connected by a straight line
 

Also, suppose that the ranges containing the X and Y values are named XVals and YVals respectively.  If necessary, use Insert | Name > Define... to name cell ranges.

Note that the x values must be sorted in ascending order.

The basics

Using linear interpolation, given any two points (x0, y0) and (x1, y1) and the value x2, where x0 x2x1 the corresponding y2 value is given by

y2 = y0 + (y1-y0) / (x1-x0) * (x2-x0)

For example, given two data points (x0, y0) = (10.75, 25.003) and (x1, y1) = (30.8, 33.33) the y value that corresponds to the x-value given by x2 = 11 would be

y2 = 25.003 + (33.33 - 25.003) / (30.8 - 10.75) * (11 - 10.75) or y2 = 25.1068

Applying the basics to a data set in Excel

Given any data set (such as the one if Figure 1 above), and a target X value, the first step is to find the 2 X-values that bracket it.  These 2 values will correspond to x0 and x1.

Suppose the target X value is given by a cell named TargetVal as in:

Use the MATCH function to find which x-value corresponds to x0 for this targetVal.

Name the cell containing the formula with the MATCH function as MatchIdx.

This is the index into the XVals range that corresponds to the x0 value.  The index for the x1 value, will be one greater than MatchIdx.  The actual x0 value is given by INDEX(XVals, MatchIdx) and the x1 value by INDEX(XVals, MatchIdx+1)

The corresponding y values, i.e., y0 and y1, are the same indices applied to the YVals range: INDEX(YVals, MatchIdx) and INDEX(YVals, MatchIdx+1)

Hence, we can now construct the desired result as:

Note this is exactly the same as = y0 + (y1-y0) / (x1-x0) * (x2-x0)

Alternative formulations

It is possible to simplify the analysis with other built-in Excel functions.  The FORECAST function is normally used for interpolation with an entire data set.  However, if its arguments are only two data points, its result will be the same as a piecewise linear interpolation.

One can also use the OFFSET function to further simplify the formula.

A VBA function

There are instances when a VBA function might be easier to use than worksheet formulas.  One instance would be error handling.  The above solutions work just fine as long as the target X value is well behaved.  Enter a value larger than the largest value in the X range and the result will be #REF! or #DIV/0!  Yes, one could expand the formula to trap errors but that would make the formula unwieldy.

An alternative formulation would be a VBA function such as the one below

Option Explicit
Option Compare Text
    Function RealEqual(X, Y) As Boolean
        RealEqual = Abs(X - Y) <= 0.00000001
        End Function
Function LinearInterp(XVals, YVals, TargetVal)
    Dim MatchVal
    On Error GoTo ErrXit
    With Application.WorksheetFunction
    MatchVal = .Match(TargetVal, XVals, 1)
    If MatchVal = XVals.Cells.Count _
            And RealEqual(TargetVal, .Index(XVals, MatchVal)) Then
        LinearInterp = .Index(YVals, MatchVal)
    Else
        LinearInterp = .Index(YVals, MatchVal) _
            + (.Index(YVals, MatchVal + 1) - .Index(YVals, MatchVal)) _
                / (.Index(XVals, MatchVal + 1) _
                    - .Index(XVals, MatchVal)) _
                * (TargetVal - .Index(XVals, MatchVal))
        End If
        End With
    Exit Function
ErrXit:
    With Err
    LinearInterp = .Description & "(Number= " & .Number & ")"
        End With
    End Function

This would be used in a Excel worksheet as:

Two dimensional interpolation

So far we have looked at interpolation where there is only 1 independent variable (given by x).  Now, we look at interpolation with 2 independent variables.  In other words, we have some variable z that depends on two independent variables, x and y.  This is given by the formula z= f(x,y).

By analogy to the 1D case, interpolation requires that we specify two points (x0, y0, z0) and (x1, y1, z1) and the value (x2, y2) where x0x2x1 and y0y2y1.  This  will let us calculate the corresponding z2 value.  The math is not presented here but it is a straightforward extension of the above case.  First, calculate two intermediate values based on the known x-values.  Then, we use these intermediate values to calculate the result using the known y-values.

One example of such a data set is shown in Figure 2-1.

In this case, one may want to find the value of the function for say x=0.5 and y= 0.5.

 

Figure 2-1

 

Another scenario where interpolation may be required is with missing data.  Instead of Figure 2-1 where each (x,y) pair has a corresponding z value, consider Figure 2-2 where not every (x,y) pair has a corresponding z value.
Figure 2-2

The VBA code below carries out piecewise linear interpolation with two independent variables.  There are two functions available for worksheet use.

The first, Interpolate2DArray, applies to the case where one can specify the known x, y, and z values in contiguous ranges.  The function itself calculates the required (x0, x1) and (y0, y1) brackets.  This corresponds to the data in Figure 2-1.

Interpolate2DArray (Z-values, X-values, Y-values, X2, Y2)

Z-values: Known z-values.  A single 2D range or a 2D array.
X-values: Known x-values.  A single 1D range (or 1D array) with the same number of rows as in Z-values.
Y-values: Known y-values.  A single 1D range (or 1D array) with the same number of columns as in Z-values.
X2, Y2: The function returns the z value corresponding to this (x,y) pair.

 An example of this function is shown below.


 

The second function, Interpolate2D, requires that the brackets be specified and would be more appropriate for Figure 2-2.

Z-values: Known z-values.  Four z values corresponding to the points (x0,y0), (x0,y1), (x1,y0), and (x1,y1).
Does not have to be a single range.
X-values: Known x-values.  Two values: x0, and x1.  Does not have to be a single range.
Y-values: Known y-values.  Two values y0, and y1.  Does not have to be a single range.
X2, Y2: The function returns the z value corresponding to this (x,y) pair.

The common code required by both functions is at top followed by the two functions themselves.

Option Explicit
Option Base 0
    Function CellAreaDecode(aRng, ByVal I As Long) As Range
        Dim AreaI As Long
        For AreaI = 1 To aRng.Areas.Count
            If I <= aRng.Areas(AreaI).Cells.Count Then
                Set CellAreaDecode = aRng.Areas(AreaI).Cells(I)
                Exit Function
            Else
                I = I - aRng.Areas(AreaI).Cells.Count
                End If
            Next AreaI
        End Function
    Sub MapIn(InVal, ByRef Where)
        Dim I As Integer, HowMany As Integer
        If Not (TypeOf InVal Is Range) Then
            Where = InVal
        ElseIf InVal.Areas.Count = 1 Then
            If InVal.Cells.Count = 1 Then
                Where = InVal.Value
            ElseIf InVal.Columns.Count = 1 Then
                Where = Application.WorksheetFunction.Transpose(InVal.Value)
            Else
                Where = Application.WorksheetFunction.Transpose( _
                    Application.WorksheetFunction.Transpose(InVal.Value))
                End If
        Else
            HowMany = InVal.Cells.Count
            ReDim Where(HowMany - 1)
            For I = 0 To HowMany - 1
                Where(I) = CellAreaDecode(InVal, I + 1).Value
                Next I
            End If
        End Sub
Function Interpolate2D(InF, InX, InY, InX2, InY2)
    'InX contains two values, x0 and x1 _
     InY contains two values, y0 and y1 _
     InF contains 4 values, defined at (x0,y0), (x0,y1), _
                                       (x1,y0), (x1,y1) _
     InX2 and InY2 define the point at which the value of _
     the function is required
    'tests to ensure x0<x2<x1 and 'y0<y2<y1 needed
    Dim F, X, Y, _
        X2 As Double, Y2 As Double
    Dim NoXVals(1)
    MapIn InF, F
    MapIn InX, X
    MapIn InY, Y
    MapIn InX2, X2
    MapIn InY2, Y2
    NoXVals(0) = (F(2) - F(0)) / (X(1) - X(0)) * (X2 - X(0)) + F(0)
    NoXVals(1) = (F(3) - F(1)) / (X(1) - X(0)) * (X2 - X(0)) + F(1)
    Interpolate2D = _
        (NoXVals(1) - NoXVals(0)) / (Y(1) - Y(0)) * (Y2 - Y(0)) _
        + NoXVals(0)
    End Function
Function Interpolate2DArray(InF, InX, InY, InX2, InY2)
    'Arguments should be in the following format.  However, currently _
     there is no validation of the arguments. _
     Each of the arguments can be either a range or an array. _
     InX is a single dimension array of x values sorted ascending. _
     InY is a single dimension array of y values sorted ascending. _
     InF is a 2D array with 1 entry for each (X, Y) pair of values in _
     the InX and InY arrays. _
     InX2 is a single value. _
     InY2 is a single value.
    Dim F, X, Y, _
        X2 As Double, Y2 As Double, _
        XIdx As Long, YIdx As Long
    Dim NoXVals(1)
    MapIn InF, F
    MapIn InX, X
    MapIn InY, Y
    MapIn InX2, X2
    MapIn InY2, Y2
    On Error GoTo ErrXIT
    XIdx = Application.WorksheetFunction.Match(X2, X, 1)
    YIdx = Application.WorksheetFunction.Match(Y2, Y, 1)
    If XIdx = UBound(X) And RealEqual(X2, X(XIdx)) Then
        If YIdx = UBound(Y) And RealEqual(Y2, Y(YIdx)) Then
            Interpolate2DArray = F(XIdx, YIdx)
        Else
            Interpolate2DArray = F(XIdx, YIdx) _
                + (F(XIdx, YIdx + 1) - F(XIdx, YIdx)) _
                    / (Y(YIdx + 1) - Y(YIdx)) * (Y2 - Y(YIdx))
            End If
    ElseIf YIdx = UBound(Y) And RealEqual(Y2, Y(YIdx)) Then
        Interpolate2DArray = F(XIdx, YIdx) _
            + (F(XIdx + 1, YIdx) - F(XIdx, YIdx)) _
                / (X(XIdx + 1) - X(XIdx)) * (X2 - X(XIdx))
    Else
        NoXVals(0) = F(XIdx, YIdx) _
            + (F(XIdx + 1, YIdx) - F(XIdx, YIdx)) _
                / (X(XIdx + 1) - X(XIdx)) * (X2 - X(XIdx))
        NoXVals(1) = F(XIdx, YIdx + 1) _
            + (F(XIdx + 1, YIdx + 1) - F(XIdx, YIdx + 1)) _
                / (X(XIdx + 1) - X(XIdx)) * (X2 - X(XIdx))
        Interpolate2DArray = NoXVals(0) _
            + (NoXVals(1) - NoXVals(0)) _
                / (Y(YIdx + 1) - Y(YIdx)) * (Y2 - Y(YIdx))
        End If
    Exit Function
ErrXIT:
    With Err
    Interpolate2DArray = .Description & "(Number= " & .Number & ")"
        End With
    End Function