1. Excel Interpolate Table Function
2. Excel Linear Interpolate Table
3. Excel Interpolate From A Table

-->

Excel Linear Interpolation Linear interpolation in excel means forecasting or guessing the upcoming next value of any certain variable given on the current data, here we create a straight line which connects two values and we estimate the future value through it, in excel we use forecast function and a lookup function to do a linear interpolation. Download ACQ.zip - 3.2 MB; Introduction. Microsoft Excel has a lot of useful functionality and is widely used for data analysis. However it does not offer any built-in functions for data interpolation.

Note

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

Summary

The following Microsoft Excel formula performs linear interpolation by calculating the interpolation step value:

=(end-start)/(ROW(end)-ROW(start))

Excel Interpolate Table Function

where end is the cell address of the larger number, and start is the cell address of the smaller number.

Interpolation is a method used to determine a present or future value factor when the exact factor does not appear in either a present or future value table. Interpolation assumes that the change between two values is linear and that the margin of error is insignificant.

Excel Linear Interpolate Table

More Information

To create a sample linear interpolation formula, follow these steps:

1. Type the following values in a worksheet:

2. Select cells A2:A6. On the Edit menu, point to Fill, and then click Down. The formula is filled down, and the following values are displayed in cells A2:A6:

Note

Excel Interpolate From A Table

You must type the reference to the step value in cell B1 as an absolute reference (with dollar signs).

VBA code to be put in a module

In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Via top menu: Insert → Module. Copy the code below into the module.
Public Function InterpolateXY(ByRef X As Range, ByRef Y As Range, ByRef XRange As Range, ByRef YRange As Range, ByRef ValueTable As Range) As Double Dim iCol As Long Dim iRow As Long Dim C1 As Double Dim C2 As Double Dim C3 As Double Dim C4 As Double Dim F1 As Double Dim F2 As Double Dim C5 As Double Dim C6 As Double 'search X iCol = 1 Do While X >= XRange(1, iCol).Value And (iCol < XRange.Columns.Count) iCol = iCol + 1 Loop 'search Y iRow = 1 Do While Y >= YRange(iRow, 1).Value And (iRow < YRange.Rows.Count) iRow = iRow + 1 Loop 'if X or Y fall outside the table, the function result is an error value If (X < XRange(1, 1).Value) Or (X > XRange(1, iCol).Value) Or (Y < YRange(1, 1).Value) Or (Y > YRange(iRow, 1).Value) Then InterpolateXY = (1 / 0) Exit Function End If '4 data table points 'around' X and Y C1 = ValueTable.Cells(iRow - 1, iCol - 1).Value C2 = ValueTable.Cells(iRow - 1, iCol).Value C3 = ValueTable.Cells(iRow, iCol - 1).Value C4 = ValueTable.Cells(iRow, iCol).Value 'X and Y fractions F1 = (X - XRange.Cells(1, iCol - 1).Value) / (XRange.Cells(1, iCol).Value - XRange.Cells(1, iCol - 1).Value) F2 = (Y - YRange.Cells(iRow - 1, 1).Value) / (YRange.Cells(iRow, 1).Value - YRange.Cells(iRow - 1, 1).Value) '2 intermediate results after interpolation on X C5 = C1 + F1 * (C2 - C1) C6 = C3 + F1 * (C4 - C3) 'final result after interpolation on Y InterpolateXY = C5 + F2 * (C6 - C5) End Function