I have an array with data:   X      Y   3     50   5     60   9    120  11    130  18     90  20    150The 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.
How to interpolate data in a range in Google Sheets
google spreadsheets;formulas
This script will do the same (plus a little bit more).Codefunction 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;}ExplainedIn 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. NoteIf the selected value equals 20, the script returns 150 as where the formula yields #DIV/0.ScreenshotFormulaUse 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))ExampleAdd 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