How to interpolate data in a range in Google Sheets

Question

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.

Answer 1

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][0] == value) {
      return y[i][0];
    } else {      
      if(x[i][0] < value && ((x[i][0] - check) < (value - check))) {
        check = x[i][0];
        index = i;
      }
    }
  }

  var xValue, yValue, xDiff, yDiff, xInt;
  yValue = y[index][0];
  xDiff = x[index+1][0] - check;
  yDiff = y[index+1][0] - 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

Answer 2

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.

Answer 3

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][0] == value) {
      return y[i][0];
    } else {      
      if(x[i][0] < value && ((x[i][0] - check) < (value - check))) {
        check = x[i][0];
        index = i;
      }
    }
  }

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

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

Answer 4

Why don't you try Interpolation AddOn? It may help.

https://workspace.google.com/u/0/marketplace/app/interpolation/47482038900

Kinds Regards