Google Sheets Formulas – How to Interpolate Data in a Range

formulasgoogle 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.

Best Answer

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

enter image description here

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

Related Topic