Method to calculate interpolation step value in Excel

The following Microsoft Excel formula performs linear interpolation bycalculating the interpolation step value
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.
More information
To create a sample linear interpolation formula, follow these steps:
  1. Type the following values in a worksheet:
       A1: 9          B1: =(A7-A1)/(ROW(A7)-ROW(A1))   A2: =A1+$B$1   A3:   A4:   A5:   A6:   A7: 11					
  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:
       A2: 9.33333   A3: 9.66667   A4: 10.   A5: 10.33333   A6: 10.66667					
NOTE: You must type the reference to the step value in cell B1 as an absolute reference (with dollar signs).
interpolate xl20 xl2000 xl2k XL2003 XL2002 XL2007

Article ID: 214096 - Last Review: 09/23/2015 06:44:00 - Revision: 7.0

Microsoft Office Excel 2007, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 2010, Excel 2016, Microsoft Excel 2013

  • kbhowto kbinfo KB214096