[ACCEPTED]-Interpolating data points in Excel-interpolation

Accepted answer
Score: 30

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

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

Things to note:

  • The formula works 9 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 8 sure that your search value is between the 7 endpoints (I do this in the example below 6 by having extreme values).

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

If 2 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 1 a named range)

Score: 6

There are two functions, LINEST and TREND, that you 7 can try to see which gives you the better 6 results. They both take sets of known Xs 5 and Ys along with a new X value, and calculate 4 a new Y value. The difference is that LINEST 3 does a simple linear regression, while TREND 2 will first try to find a curve that fits 1 your data before doing the regression.

Score: 5

The easiest way to do it probably is as 5 follows:

  1. Download Excel add-on here: XlXtrFun™ Extra Functions for Microsoft Excel

  2. Use 4 function intepolate(). =Interpolate($A$1:$A$3,$B$1:$B$3,D1,FALSE,FALSE)

Columns 3 A and B should contain your input, and column 2 G should contain all your date values. Formula 1 goes into the column E.

Score: 2

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

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

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

Score: 2

The answer above by YGA doesn't handle end 17 of range cases where the desired X value 16 is the same as the reference range's X value. Using 15 the example given by YGA, the excel formula 14 would return #DIV/0! error if an interpolated 13 value at 9999 was asked for. This is obviously 12 part of the reason why YGA added the extreme 11 endpoints of 9999 and -9999 to the input 10 data range, and then assumes that all forecasted 9 values are between these two numbers. If 8 such padding is undesired or not possible, another 7 way to avoid a #DIV/0! error is to check 6 for an exact input value match using the 5 following formula:

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

where F3 is the value 4 where interpolated results are wanted.

Note: I 3 would have just added this as a comment 2 to the original YGA post, but I don't have 1 enough reputation points yet.

Score: 1

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 2 range of x values yvals is range of y values

easier 1 to put this into code.

Score: 1

You can find out which formula fits best 5 your data, using Excel's "trend line" feature. Using 4 that formula, you can calculate y for any 3 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 2 holds y's, and C12 contains formula that 1 calculates y for any x.

Excel Interpolation

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

More Related questions