XL: Method to Calculate Interpolation Step Value

Last reviewed: February 2, 1998
Article ID: Q95479

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0, 5.0c
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel 98 Macintosh Edition
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0, 5.0a

SUMMARY

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

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

In the above formula, "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:

Enter 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

Select cells A2:A6. On the Edit menu, click Fill 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 enter the reference to the step value in cell B1 as an absolute reference (with dollar signs).

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 364 "Microsoft Excel Function Reference," version 3.0, page 201 "Microsoft Excel for Windows Functions and Macros," version 2.10, pages 104-105

Additional query words: 3.00 4.00 4.00a 5.00 5.00a 5.00c 7.00 7.00a 97 98
XL98 XL97 XL7 XL5 XL4 XL3 interpolate
Keywords : xlformula
Version : WINDOWS:2.0,3.0,4.0,4.0a,5.0,5.0c,7.0,7.0a,97; MACINTOSH:2.0,3.0,4.0,5.0,98; os/2:2.2,3.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.