Download the Linear Interpolation Extrapolation Template (Plus Tutorial)

Click here to download the Linear Interpolation Extrapolation Template with Chart (Free template with full functionality)

Wikipedia definition: In mathematics, linear interpolation is a method of curve fitting using linear polynomials. For more info: http://en.wikipedia.org/wiki/Linear_interpolation

Introduction to the Excel Template

This template allows you to quickly perform a linear interpolation (and extrapolation) between a flexible data set, up to a maximum of 50 sample data points (rows). In plain English, this Excel template draws a straight line between each point in a data set. Also, it calculates (or approximates) the coordinates of points that forms those straight line.

The worksheet is flexible, so if you are willing to perform certain changes, it can accommodate a much larger data set.The user can control three things for interpolation:

• Starting x-value
• Ending x-value
• Size of increment

Note that the chart can also extrapolate data beyond the stated data set, assuming the same slope applies past the last data point.

Below is a thorough explanation on how to properly use the sample file.

Existing Base Template

• Column A-C:
  • Sample data point to be interpolated. All sample data must be entered as per the headings (x-axis, y-axis in the corresponding blue area)
  • Important:
    • Numbers in Column A MUST be in ASCENDING ORDER, otherwise this will NOT work.
    • Numbers in Column A must all be different (or unique). No number can show up more than once.
  • The slope in column C is calculated. Do not edit or delete any cells.
    • The slope formula automatically adjusts based on the number of data points.
    • You do not need to edit this row, as low as the number data points remain at or below 50.
  • Without any edits, the chart file should have 11 sample data points or entries on the left.
• Column E-F
  • This is where the interpolation calculation occurs, with the help of columns H to K.
    • We won’t explain Columns H to K here. All you need to know is that they are support calculations.
    • Anyone who is interested in the linear interpolation formula should be able to decipher it!
  • Column E starts, increments and ends based on user input in column O.
  • Column F is the interpolated Y-axis. Note that for any x-value that is equal to a sample data point’s y-value. Otherwise the file is wrong (or I made a mistake :))
    • For example, in the original file in sample data (column A and B): widgets (x-axis) = 100, time (y-axis) = 1300. If in Column E and F, whenwidgets (x-axis) is 100, but interpolated time (y-axis) is not 1300, then the file is wrong.
    • The “Chart with Sample Data AND Interpolation (and Extrapolation)” – blue small dots (interpolation extrapolation) should fit well with the red dots (sample data).
• Column O
  • User configuration as described above

How to Add More Sample Data

1. Add the new data point (x and y axis) into columns A and B accordingly.
2. Make sure x-axis (column A) is always in ascending order.
3. If you have to shift cells, do NOT cut and paste, as this will mess up the formulas.
   1. Instead, copy and paste over. Then overwrite the target row with the new data.
   2. If you do not know how to do that, just copy the sample data to a new file first, fix everything, then paste everything back into the blue area.
   3. As long as you do not CUT anything, the formulas will not break.