8

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

11
  • What would the equation's arguments to be and what would it return? VLOOKUP could be used or INDEX and MATCH? A lookup by itself won't interpolate any data, if an exact match is not wanted, both VLOOKUP and MATCH can return the largest value that is less than or equal to lookup_value. For that kind of match, the column that the functions look in for a match must be sorted. You need to use MATCH so that you can get the values less than and greater than your lookup argument. The column you look into must then have to be sorted - otherwise, what proportion would you use for the interpolation?
    – chuff
    Jul 28 '13 at 12:27
  • I'd check mathematica.stackexchange.com Jul 28 '13 at 12:28
  • @Raystafarian then perhaps someone should migrate it?
    – dassouki
    Jul 28 '13 at 12:35
  • @chuff does my edit to the question help?
    – dassouki
    Jul 28 '13 at 12:36
  • It's not a standard migration option. If you feel it'd get more awareness at another site, you can flag your own post and request a migration from a mod. I only suggested it because it might be of value to search there, but you might get your answer here too. Jul 28 '13 at 12:53
6

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.

Table for which interpolations will be calculated

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.

Remaining step to calculate interpolated value for input value pair

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.

Two-way data 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.

2
  • Generally good answer except the data is very non-linear, so linear interpolation won't give an accurate result. The results would be better combining this with non-linear interpolation.
    – fixer1234
    Nov 18 '16 at 20:41
  • 1
    @fixer1234: With all due respect, I’d give this comment a lot more weight if it came from the OP, or were based on something the OP said.  As it is, we don’t really know what the OP wants — see my comments on the question.
    – Scott
    Nov 7 '17 at 1:41
3

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.

Excel Interpolation

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.

1
  • You're on a right track. To take this to the second dimension, you could use this approach to create a family of trend lines representing either the rows or columns. Estimate the Y value for each trend line. Then repeat the process on those values to interpolate in the other direction.
    – fixer1234
    Nov 18 '16 at 20:36
2

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):

enter image description here

1
  • Works like charm. Thanks for sharing!
    – Tung
    Jun 10 '19 at 19:08
1

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

enter image description here

3
  • Tip: use backticks or four-space indentation when entering code. It looks much nicer that way. :-) Jul 28 '13 at 12:52
  • Match type -1 returns the the smallest value in the lookup column that is greater than or equal to the lookup value. The lookup column (or row) be must sorted in descending order to work properly. To see the problem, change the y value to 0.15 or 0.2: The ANS formula returns the error value #N/A. Even if the data were rearranged to address the sort order issue, the formula still does not compute interpolated values, i.e., values that lie between two of the values in B3:D6: For example, an interpolated value for 2.27 would for column B be some amount greater than 500 and less than 550.
    – chuff
    Jul 28 '13 at 19:46
  • @chuff is right, this doesn't really answer the question.
    – fixer1234
    Nov 18 '16 at 20:28
1

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.

1
  • This would work if the example was linear, or close to it. This data is extremely non-linear. For results with any kind of accuracy, you would need to build on @alexkovelsky's approach.
    – fixer1234
    Nov 18 '16 at 20:26
1

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

1
  • This appears to duplicate Yasir Alharthi's answer. Also, please review the earlier discussions of the non-linearity of the data.
    – fixer1234
    Jun 1 '18 at 4:45
0

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

Excel spreadsheet details

0

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.

  1. 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))

Excel Solution

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.