Function Problems

Interpolate Values Between Two Known Points in Excel

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

Calculate intermediate results between two known input and result pairs using linear interpolation.

Interpolate Values Between Two Known Points in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user has two known points and wants to estimate results for input values between them.
Observed behavior
Intermediate results should follow a straight line between the two known points.
Before you start

Enter the known inputs and results in A2:B2 and A10:B10, and enter the intermediate inputs in A3:A9. Make sure the two endpoint inputs are different to avoid division by zero.

Solution 1Recommended

Use a Linear Interpolation Formula in Excel

Use the two endpoint values to calculate results for inputs between them.

1
Enter the known points

Enter 12 in A2 and 700 in B2, then enter 36 in A10 and 300 in B10.

2
Enter intermediate inputs

Type the input values you want to calculate in A3:A9. For example, enter 24 in A3.

3
Add the formula

Select B3 and enter =$B$2+(A3-$A$2)*($B$10-$B$2)/($A$10-$A$2). For an input of 24, the result is 500.

4
Copy the formula down

Drag the fill handle from B3 through B9. The dollar signs keep the endpoint references fixed while the input reference changes for each row.

Use a Linear Interpolation Formula in Excel
Round the results: For whole-number results, enter =ROUND($B$2+(A3-$A$2)*($B$10-$B$2)/($A$10-$A$2),0) in B3. Use semicolons instead of commas if your regional settings require them.
Calculate in WPS Spreadsheet

Interpolate Values in WPS Spreadsheet

Use the same endpoint-based formula in WPS Spreadsheet to estimate intermediate results and fill the formula down a column.

  1. 1. Open a spreadsheet: Open WPS Spreadsheet and create a workbook or open an existing Excel file.
  2. 2. Enter the data: Place the endpoint inputs in A2 and A10, their results in B2 and B10, and the intermediate inputs in A3:A9.
  3. 3. Enter the formula: Select B3 and enter =$B$2+(A3-$A$2)*($B$10-$B$2)/($A$10-$A$2).
  4. 4. Fill down: Drag the fill handle from B3 through B9 to calculate the remaining results.
Calculate intermediate values with a linear interpolation formulaOpen and save Microsoft Excel XLSX filesCopy formulas down with fixed endpoint referencesUse familiar spreadsheet tools for free
microsoft office alternative - wps office

Frequently Asked Questions

What result should I get for an input of 24?

With endpoints 12 and 700, and 36 and 300, enter 24 in A3. The interpolation formula returns 500.

How do I round interpolated values to whole numbers?

Wrap the formula in ROUND, for example: =ROUND($B$2+(A3-$A$2)*($B$10-$B$2)/($A$10-$A$2),0).

Can I use the formula for inputs outside the two endpoints?

Yes, but the result is an extrapolation rather than an interpolation. It extends the same straight-line relationship beyond the known points.

Why does the formula return a division error?

Check A2 and A10. If the endpoint inputs are equal, their difference is zero and the formula cannot divide by it.