What is the simplest way to interpolate and lookup in an x,y table in Excel?

Question

I would like to do a lookup and interpolation based on x, y data for the following table. I'd like the equation to be as simple as possible to reduce the amount of possible errors. The full table is about 50 rows x 30 columns. I have about 20 of those tables. Here is an extract from one:

   A       B      C    D 
1        0.1    0.2   0.3 
2  2.4   450    300   50
3  2.3   500    375   52
4  2.1   550    475   55
5  1.8   600    600   60

For example, the equation should find the value for x = 2.27 and y = 0.15

Answer 1

You have a number of tables that have index values in the first row and first column of each table and a set of values in the interior of the table, each of which is associated with specific row and column index values.

Given two values that, respectively, may equal exactly a row or column index, or may lie between two row index values or two column index values, you want to do a straight-line interpolation of the values in the table based upon the two given values for the first row and first column.

To do the interpolation for input values that lie between the index values in the table, the following are needed:

• The row number of the smallest vertical index value that is greater than (or equal to) the vertical input value vnum. Since the index values in column 1 of the table are in descending order, this can be obtained with:

  =MATCH(vnum,vrng,-1)

where vnum is the input value and vrng is the vertical range of indexes in the first column of the table. The -1 third argument of theMATCH function indicates that a "less than" lookup will be performed. This use of the MATCH function requires that the vertical range of indexes be in descending order.

• The largest vertical index value that is smaller than (or equal) the vertical input value (vnum). This cannot be obtained using the MATCH function because the vertical index values are not sorted in the ascending order required by MATCH. Instead, the following array formula is used.

  =MIN(IFERROR(1/(vnum>=vrng)*ROW(INDIRECT("1:"&ROWS(vrng))),ROWS(vrng)))

The key element in this formula is vnum>=vrng, which produces a boolean array in which the first TRUE is in the row position that holds the largest vertical index value that is less than the input value. (The use of ">=" may seem counter-intuitive; it's needed because the indexes in the column are in descending order.) The remainder of the formula converts this row position into a row number.

These two upper and lower row numbers are used to calculate both the index values that bracket the input value above and below and the interior table values corresponding to those index values.

The corresponding column numbers and column values for the horizontal index range are constructed in a similar fashion.

The remaining steps work through the arithmetic of the interpolation.

With this many steps, it may seem that calculating the interpolated amounts for multiple input values would be impractical. It's actually quite easy using a two-way data ("what-if") table.

The setup of these calculations for multiple tables can be simplified even further by using the one-formula version of them. To use it, the named ranges vnum, hnum, vrng, hrng, datarng, validvnum, and validhnum would need to be set up. The tables need to be in separate sheets or in separate workbooks. If in separate sheets, the names for each sheet must be set to have worksheet scope.

The single-step calculation formula would then be entered in the top left cell of the data table. This 2,100 (minus one)-character formula is included in the downloadable worksheet.

A worksheet containing this set of calculations can be downloaded using this link.

Answer 2

Actually you have a 3-d table - x and y are independent variables, and the one you need to find is z.

I know a solution for 2-d table, but for 3-d it should be similar.

So if you have 2-d table, 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.

As for your case (3-d data), Excel has capability to build 3-d charts also, but I don't know if they have 3-d trends.

Answer 3

I have written VBA code for bilinear interpolation which you can find here: https://github.com/DanGolding/Linear-and-bilinear-interpolation-in-Excel

The advantage of this method is that it is a single function which makes it easy to interpolate a grid of values as shown in this image (note the cells are coloured according to the values to better visualise the effect of the interpolation):

Answer 4

Use Index and Match function of excel in combination to achieve your result. Using the above data have placed exactly in excel and derived the following formula. Formula is entered in cell D8.

=INDEX($B$3:$D$6,MATCH(E2,$A$3:$A$6,-1),MATCH(F2,$B$2:$D$2,-1))

Answer 5

Here is the formula you're looking for: Given two points (x1,y1) and (x2,y2), to interpolate between them, use the formula:

y = (y2-y1)/(x2-x1)*(x-x1) + y1

where x is the input and y is the output. Given the following table

The formula is: =IF(ISERROR(MATCH(O3,R$4:R$10)),S$4,IF(ISERROR(INDEX(R$4:R$10,MATCH(O$3,R$4:R$10)+1)),S$10,(INDEX(R$4:S$10,MATCH(O3,R$4:R$10)+1,2)-INDEX(R$4:S$10,MATCH(O3,R$4:R$10),2))/(INDEX(R$4:S$10,MATCH(O3,R$4:R$10)+1,1)-INDEX(R$4:S$10,MATCH(O3,R$4:R$10),1))*(O3-INDEX(R$4:S$10,MATCH(O3,R$4:R$10),1)) + INDEX(R$4:S$10,MATCH(O3,R$4:R$10),2)))

The iferror functions handle the end points. They keep the value at the end point rather than extrapolating.

Answer 6

The best answer ever for this problem is found in this video https://www.youtube.com/watch?v=LFUd5qF8nyE

The final formula is

=FORECAST(C3,OFFSET(interpol_array,MATCH($C3,lookup_array,1)-1,0,2),OFFSET(lookup_array,MATCH($C3,lookup_array,1)-1,0,2))

Where the "interpol_array" is defined as the column you want to interpolate the values from and "lookup_array" is the column you search for the values.

This answer also states the same thing https://stackoverflow.com/questions/42877228/vlookup-and-interpolating

Answer 7

It is solved with three functions combined; Forecast, Offset, and Match.

=FORECAST($B$9,OFFSET(B3:D3,0,MATCH($B$9,$B$2:$D$2,1)-1,1,2),OFFSET($B$2:$D$2,0,MATCH($B$9,$B$2:$D$2,1)-1,1,2))

=FORECAST(B10,OFFSET(E3:E6,MATCH(B10,A3:A6,-1)-1,0,2),OFFSET(A3:A6,MATCH(B10,A3:A6,-1)-1,0,2))

Answer 8

One Simple way of Solving this problem is by using the Forecast.Linear, Index & Match Functions.

1. Use the following Formula in the cell I2 to interpolate for the value 0.15(entered in cell I1)

=FORECAST.LINEAR($I$1,INDEX(F2:H2,MATCH($I$1,$F$1:$H$1,1)):INDEX(F2:H2,MATCH($I$1,$F$1:$H$1,1)+1),INDEX($F$1:$H$1,MATCH($I$1,$F$1:$H$1,1)):INDEX($F$1:$H$1,MATCH($I$1,$F$1:$H$1,1)+1))

The "$" sign keeps the values constant when the formula is dragged to next cells i.e upto the cell I5.

2. Use the Formula in the cell F7 to interpolate for the value 2.27 (entered in cell E7) =FORECAST.LINEAR(E7,INDEX(I2:I5,MATCH(E7,E2:E5,-1)):INDEX(I2:I5,MATCH(E7,E2:E5,-1)+1),INDEX(E2:E5,MATCH(E7,E2:E5,-1)):INDEX(E2:E5,MATCH(E7,E2:E5,-1)+1))