# [ACCEPTED]-Interpolating data points in Excel-interpolation

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
`OFFSET`

s 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

- using the
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)

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.

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

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

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.

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.

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.

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.

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`

- Create linear scatter (XY) for it (Insert => Scatter);
- Create Polynominal or Moving Average trend line, check "Display Equation on chart" (right-click on series => Add Trend Line);
- 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`

.

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

More Related questions

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.