Interpolation function for google sheets

google-sheets-interpolate.gs

// 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";
}