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.



Answers

You can find out which formula fits best your data, using Excel's "trend line" feature. Using that formula, you can calculate y for any x

  1. Create linear scatter (XY) for it (Insert => Scatter);
  2. Create Polynominal or Moving Average trend line, check "Display Equation on chart" (right-click on series => Add Trend Line);
  3. Copy the equation into cell and replace x's with your desired x value

On screenshot below A12:A16 holds x's, B12:B16 holds y's, and C12 contains formula that calculates y for any x.

Excel Interpolation

I first posted an answer here, but later found this question



alternatively.

=INDEX(yVals,MATCH(J7,xVals,1))+(J7-MATCH(J7,xVals,1))*(INDEX(yVals,MATCH(J7,xVals,1)+1)-INDEX(yVals,MATCH(J7,xVals,1)))/(INDEX(xVals,MATCH(J7,xVals,1)+1)-MATCH(J7,xVals,1))

where j7 is the x value.

xvals is range of x values yvals is range of y values

easier to put this into code.

By : darren


A nice graphical way to see how well your interpolated results fit:

Take your date,value pairs and graph them using the XY chart in Excel (not the Line chart). Right-click on the resulting line on the graph and click 'Add trendline'. There are lots of different options to choose which type of curve fitting is used. Then you can go to the properties of the newly created trendline and display the equation and the R-squared value.

Make sure that when you format the trendline Equation label, you set the numerical format to have a high degree of precision, so that all of the significant digits of the equation constants are displayed.

By : Stewbob


This video can help you solving your question :)
By: admin