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

Solution 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.

Solution 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.