How to interpolate data in a range in Google Sheets

I have an array with data:

X      Y
3     50
5     60
9    120
11    130
18     90
20    150

The data is entirely non-linear. X is guaranteed to be sorted.

Now for any given value, I'd like to have linear interpolation between the numbers (so for example, 3 => 50, 4 => 55, 5 => 60). A bilinear interpolation would be even nicer, but I'm keeping my expectations low.

This script will do the same (plus a little bit more).

Code

function myInterpolation(x, y, value) {
if(value > Math.max.apply(Math, x) || value < Math.min.apply(Math, x)) {
throw "value can't be interpolated !!";
return;
}

var check = 0, index;
for(var i = 0, iLen = x.length; i < iLen; i++) {
if(x[i] == value) {
return y[i];
} else {
if(x[i] < value && ((x[i] - check) < (value - check))) {
check = x[i];
index = i;
}
}
}

var xValue, yValue, xDiff, yDiff, xInt;
yValue = y[index];
xDiff = x[index+1] - check;
yDiff = y[index+1] - yValue;
xInt = value - check;

return (xInt * (yDiff / xDiff)) + yValue;
}

Explained

In the beginning of the script, there's a small error handling. After that it will find the first lowest entry compared to the input value. Once found, it will does some math and present the result.

Note

If the selected value equals 20, the script returns 150 as where the formula yields #DIV/0.

Screenshot Formula

Use the following formula to take in account all values

=IF(
ISNA(
MATCH(C2,A2:A7,0)),
FORECAST(
\$C\$2,
OFFSET(B\$2,MATCH(\$C\$2,A\$2:A\$7,1)-1,0,2,1),
OFFSET(A\$2,MATCH(\$C\$2,A\$2:A\$7,1)-1,0,2,1)),
INDEX(
B2:B7,
MATCH(C2,A2:A7,0)
,0)
)

copy / paste
=IF(ISNA(MATCH(C2, A2:A7, 0)), FORECAST(\$C\$2,OFFSET(B\$2,MATCH(\$C\$2,A\$2:A\$7,1)-1,0,2,1),OFFSET(A\$2,MATCH(\$C\$2,A\$2:A\$7,1)-1,0,2,1)), INDEX(B2:B7, MATCH(C2, A2:A7, 0), 0))

Example

Add the script under Tools>Script editor and press the save button (no authentication needed).

I've created an example file for you: How to interpolate data in a range in Google Sheets

• Thanks Jacob! To be honest, I almost like my version better because it works on mobile devices as well (the native Android Sheets app and the mobile version of the web app don't have script support afaict), but your function is definitely cleaner and more elegant. So I'm picking your answer. – EboMike Jul 9 '14 at 1:10
• @EboMike I looked at my code and noticed an error. I revised the code and came up with a formula, so that you can use it on your mobile app. – Jacob Jan Tuinstra Jul 9 '14 at 10:36
• And this is why it's unfortunate you can't upvote an answer more than once :) Thanks Jacob. – EboMike Jul 9 '14 at 15:35

I found a way to do it - there may be a better way, but this is what I came up with:

Assuming the data is in A1:B10 and \$C\$1 contains the key to look for:

=FORECAST(\$C\$1,
OFFSET(B\$1,MATCH(\$C\$1,A\$1:A\$10,1)-1,0,2,1),
OFFSET(A\$1,MATCH(\$C\$1,A\$1:A\$10,1)-1,0,2,1))

In detail:

FORECAST does a linear interpolation, but it assumes a straight line. So we need to find the two values that enclose the value we're looking for.

So we use MATCH to find the first number that is equal or higher to what we're looking for.

FORECAST expects a data range, so we use OFFSET to create a reference to a data range. MATCH is one-indexed, so we need to subtract one first. We create a range that is one wide and two high. This value is guaranteed to enclose \$C\$1, our search value.

• +1; nice formula !! Choosing x=20 will result in #DIV/0. – Jacob Jan Tuinstra Jul 7 '14 at 12:48

This is a small modification of Jacob Jan Tuinstra's script, enabling it to take either an array or a value as the third argument, so that interpolated function can be computed at many places at once. The only difference is a few lines added at the beginning; this is a quick way to turn pretty much any custom function into a custom function that accepts an array.

function myInterpolation(x, y, value) {
if (value.map) {
return value.map(function(v) {
return myInterpolation(x, y, v);
});
}
//  the rest stays the same

if (value > Math.max.apply(Math, x) || value < Math.min.apply(Math, x)) {
throw "value can't be interpolated !!";
return;
}

var check = 0, index;
for(var i = 0, iLen = x.length; i < iLen; i++) {
if(x[i] == value) {
return y[i];
} else {
if(x[i] < value && ((x[i] - check) < (value - check))) {
check = x[i];
index = i;
}
}
}

var xValue, yValue, xDiff, yDiff, xInt;
yValue = y[index];
xDiff = x[index+1] - check;
yDiff = y[index+1] - yValue;
xInt = value - check;

return (xInt * (yDiff / xDiff)) + yValue;
}