Skip to content

Instantly share code, notes, and snippets.

@0x4C4A
Last active April 3, 2022 19:55
Show Gist options
  • Save 0x4C4A/ae8c4d8fc38ee9d28fca559d9710303b to your computer and use it in GitHub Desktop.
Save 0x4C4A/ae8c4d8fc38ee9d28fca559d9710303b to your computer and use it in GitHub Desktop.
Interpolation function for google sheets
// Based on http://www.jamesphoughton.com/2012/10/10/linear-interpolation-with-dates-in.html
// Added some extra error checks, refactored a bit
// #####################
// How To Use
// 1. In google sheets, open Tools -> Script Editor
// 2. Copy this code in the Script Editor
// 3. Save
// 4. Enjoy using this as you would any other function in the sheet (the app will show that it doesn't know this function, but if you typed it correctly it will work)
// Example cell content: =interpolate(A2:A15, B2:B15, 13)
// #####################
// Notes
// This uses parseFloat to parse numbers, so % will lose their percent sign, as will currencies etc.
// If this is important to you, you can either fix it yourself or drop me a line.
// #####################
function interpolate(xstr, ystr, xvalstr)
{
const x = [];
let increasing = undefined;
for(const i in xstr){
const val = parseFloat(xstr[i]);
if(isNaN(val))
continue;
x[x.length] = val;
if(x.length < 1)
continue;
const prevX = x[x.length - 2];
const currX = x[x.length - 1];
if(((increasing === true) && (currX < prevX)) ||
((increasing === false) && (currX > prevX)))
return "err: input x array isn't monotonically in/de-creasing"
else
increasing = currX > prevX;
}
const y = [];
for(const i in ystr){
const val = parseFloat(ystr[i]);
if(isNaN(val))
continue;
y[y.length] = val;
}
if(x.length != y.length)
return "err: input arrays' lengths do not match (NaNs are ignored)";
const lowX = increasing ? x[0] : x[x.length-1];
const highX = increasing ? x[x.length-1] : x[0];
const xval = parseFloat(xvalstr);
if(xval > highX)
return "err: xval out of bounds high";
if(xval < lowX)
return "err: xval out of bounds low";
for(let i = 0; i< x.length - 1; i++)
if((x[i]<=xval && x[i+1]>=xval) || (x[i]>=xval && x[i+1]<=xval))
return (xval-x[i])*(y[i+1]-y[i])/(x[i+1]-x[i])+y[i];
return "unknown error - debug the function";
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment