Linear Interpolation in Excel

We often need to find a value between or near two other known values. If we do not have a more accurate curve, a straight line will have to do. This is Linear Interpolation (or extrapolation). Excel is a good way to do it.

For example, I read our electricity meters weekly. This lets me check up on our electricity utility. But I seldom read on the same day as they do, and never at the same time. So how do I know if their readings are reasonable? I interpolate between my two adjacent readings, and compare the result with theirs. We’ll look at that practical application later.

But first, some theory –and the formula you are looking for…

Look at this chart:

Our known points are A (coordinates x₁, y₁) and C (coordinates x₂, y₂).

For known value x, we want to interpolate y. In other words, we want to find point E on the straight line AC.

From the diagram, y = y₁ + DE (Formula 1).

Triangles ABC and ADE are similar triangles. Their angles are identical and their sides are in the same ratio.

Hence, DE / AD = BC / AB
Thus, DE = AD * BC / AB

From Formula 1, y = y₁ + BC * AD / AB
Substituting x and y values from the diagram:
The formula for linear interpolation is: y = y₁ + (y₂ – y₁) * (x – x₁) / (x₂ – x₁)
where:
x is the known value,
y is the unknown value,
x₁, y₁ are coordinates below the known value,
x₂, y₂ are coordinates above the known value.

The formula still works if you swap x₁ with x₂ and y₁ with y₂.

Simple Implementation in Excel

Say that, for example, we want to interpolate an electricity meter reading between two dates:

To make the formula more obvious, we have named the cells as listed in the Range Name Table. The dollar signs are irrelevant. We named cells x_1, x_2, etc. and not x1, x2, etc. because the latter are not allowed, as they would conflict with cell addresses. Remember that the underscore (“_”) is allowed in Range Names, but not spaces.

The formula for y, then, becomes: =y_1+(y_2-y_1)*(x-x_1)/(x_2-x_1)

Or, if we want whole numbers only, we can round the result: =ROUND(y_1+(y_2-y_1)*(x-x_1)/(x_2-x_1),0)

To demonstrate this graphically, we can create a scatter chart for the range A4:B6. A scatter chart is the only type that measures on both the x and y axes:

Practical Application

For example, I read our electricity meters weekly. This lets me check up on our electricity utility. But I seldom read on the same day as they do, and never at the same time. So how do I know if their readings are reasonable? I can interpolate between my two adjacent readings, and compare the result with theirs. We’ll look at this practical application now.

Here is my spreadsheet:

I read the meters four times a month. The date and time are in column A. In column B, the difference between successive date-times gives us the number of days. We have three-phase power, hence three meters: Blue, white and red. They are in columns C:E. We also have solar panels, the production of which I read at the same time. In columns G:K, I divide the preceding four columns by the number of days to get a daily kWh figure. For compactness, I have hidden columns L:O, which contain the monthly equivalents.

On the right-hand side, opposite the nearest date, we have the date (time unknown) and readings taken by the utility. Sometimes they estimate readings, and at other times they take actual readings. On the row below the utility’s readings (rows 374 and 378 in the example), we do a linear interpolation from my own readings. On the row below that, I calculate the difference. The difference in cell S375 is highlighted using Conditional Formatting if the value exceeds 10.

The formula in cell S374, as shown, is =C373+(C374-C373)*($R373-$A373)/($A374-$A373)

Note the dollar signs. They prevent column letters A and R (the dates) from changing when we copy the formula to the right.

We can’t use range names in this example, but if you compare this formula with the one above, you will see that it corresponds to =y_1+(y_2-y_1)*(x-x_1)/(x_2-x_1)

Happy interpolating!