James P Houghton

James Houghton - Linear interpolation with Dates in Google Spreadsheets

Linear interpolation with Dates in Google Spreadsheets

10 Oct 2012

Today I needed to interpolate time-series data in a Google Spreadsheet, and wrote a script to do so for me.

You can use it too by including the project code: MzfyV3-6hnRz74zg_T_ocF9ABNozjJE6d into your projects.

Alternately, to use the interpolate functions in Google spreadsheets, visit the script gallery under the Tools menu:

and search for "Interpolate":
When you install the script, you'll need to authorize it to run on your spreadsheet.

Interpolate over X-Y values:
   interp(xstr, ystr, xvalstr)
 Performs linear interpolation on the data set with x values listed as strings (because this is how Google spreadsheets passes them to the function) in the first argument, and y values (as strings) in second argument. For example, in the following spreadsheet, xstr = A2:A21 and ystr = B2:B21:
The third argument is the lookup value, also as a string.  If I want to look up a value from a particular cell E4,  I would set xvalstr = E4:

my call to the interpolate function would be:

Interpolate over time series data
   interpdate(tstr, ystr, tvalstr)
When you have data in which the independent variable is formatted as a date/time string, you need to use a different method than when interpolating purely over numeric data. Use interpdate if your data is akin to the following:

Here's a link to a google spreadsheet demonstrating both functions in action. Happy computing!

© 2016 James P. Houghton