Interpolating data points in Excel

Question

I'm sure this is the kind of problem other have solved many times before.

A group of people are going to do measurements (Home energy usage to be exact). All of them will do that at different times and in different intervals.

So what I'll get from each person is a set of {date, value} pairs where there are dates missing in the set.

What I need is a complete set of {date, value} pairs where for each date withing the range a value is known (either measured or calculated). I expect that a simple linear interpolation would suffice for this project.

If I assume that it must be done in Excel. What is the best way to interpolate in such a dataset (so I have a value for every day) ?

Thanks.

NOTE: When these datasets are complete I'll determine the slope (i.e. usage per day) and from that we can start doing home-to-home comparisons.

ADDITIONAL INFO After first few suggestions: I do not want to manually figure out where the holes are in my measurement set (too many incomplete measurement sets!!). I'm looking for something (existing) automatic to do that for me. So if my input is

{2009-06-01,  10}
{2009-06-03,  20}
{2009-06-06, 110}

Then I expect to automatically get

{2009-06-01,  10}
{2009-06-02,  15}
{2009-06-03,  20}
{2009-06-04,  50}
{2009-06-05,  80}
{2009-06-06, 110}

Yes, I can write software that does this. I am just hoping that someone already has a "ready to run" software (Excel) feature for this (rather generic) problem.

Answer 1

I came across this and was reluctant to use an add-in because it makes it tough to share the sheet with people who don't have the add-in installed.

My officemate designed a clean formula that is relatively compact (at the expensive of using a bit of magic).

Things to note:

• The formula works by:

  • using the MATCH function to find the row in the inputs range just before the value being searched for (e.g. 3 is the value just before 3.5)
  • using OFFSETs to select the square of that line and the next (in light purple)
  • using FORECAST to build a linear interpolation using just those two points, and getting the result

• This formula cannot do extrapolations; make sure that your search value is between the endpoints (I do this in the example below by having extreme values).

Not sure if this is too complicated for folks; but it had the benefit of being very portable (and simpler than many alternate solutions).

If you want to copy-paste the formula, it is:

=FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1

(inputs being a named range)