Double interpolation for a large set of data in Excel

Question

How do you do double interpolation for a large set of data in Excel?

The user is required to input an x value and y value then the program has to find the points and do interpolation if necessary. For example, if the x is 3.5 and y value is 4.5 then I'll have 2 x value and 2 y values so I need to do the double interpolation then final interpolation between the remaining values.

I have used a vlookup function and forecast function but it doesn't seem to be working out. I have attached a picture of the data set.

enter image description here

Take it in steps, interpolate for the x values - once that is complete then do y.... and yes you can do it but it gets tricky so is good practice... — Solar Mike, Apr 20 '18 at 13:53
When you use vlookup, get it to return the values either side of the target etc... I used it on air density values as they change with pressure and temperature for evaluating engine performance. — Solar Mike, Apr 20 '18 at 13:56
Seriously - dump Excel and use a real tool. R, python, Matlab, Mathematica, Macsyma, etc. 2-dimensional fits are built-in. — Carl Witthoft, Apr 20 '18 at 14:20
Your screengrab is illegible. If you can provided a subset of your dataset and explain your interpolation algorithm a bit better then I'm sure a VBA function should be able to do what you require. — Transistor, Apr 21 '18 at 09:13

Transistor · Accepted Answer · 2018-04-21T16:27:10.793

It appears that your dataset has integer row and column header values starting at 0. You can try the following code.

Turn on Excel's Developer ribbon if it's not already on.
Click the Visual Basic button.
Insert | Module.
Paste the code below into the module.
Adjust the rowOffset value to suit.
Enter the formula =Interpol2D(y, x) into your spreadsheet where you want the interpolation result displayed.
Save as .xlsm (with macro).
Every time you change one of the referenced values the calculation will be run again.
You can monitor the calculated values in the Visual Basic Immediate window.

Code:

Function Interpol2D(r, c)           'Row and column.
'By Transistor.
'https://engineering.stackexchange.com/questions/21400/double-interpolation-for-a-large-set-of-data-in-excel/21414#21414

    rowOffset = 10                  'The row number for the header line of the table.
    r1 = Int(r + rowOffset)         'The table row number for the first parameter
    r2 = Int(r) + rowOffset + 1     'The next row.
    c1 = Int(c) + 2                 'The table column number for the second parameter
    c2 = Int(c) + 3                 'The next column.

    'Layout of the four adjacent cells and the interpolated result.
    '    (r1, c1) *---p-------* (r1, c2)
    '                 |
    '                 |
    '                 r (result)
    '                 |
    '    (r2, c1) *---q-------* (r2, c2)

    r1c1 = ActiveSheet.Cells(r1, c1)
    r2c1 = ActiveSheet.Cells(r2, c1)
    r1c2 = ActiveSheet.Cells(r1, c2)
    r2c2 = ActiveSheet.Cells(r2, c2)

    'Interpolate by multiplying the difference between the two cells by the fractional
    'part of the parameter and then add in the first cell value.
    p = (r1c2 - r1c1) * (c - Int(c)) + r1c1    'The interpolation along the first horizontal.
    q = (r2c2 - r2c1) * (c - Int(c)) + r2c1    'The interpolation along the second horizontal.
    r = (q - p) * (r - Int(r)) + p             'The interpolation along the vertical.

    'Results will be printed out in the Immediate window.        
    Debug.Print "r1c1:", r1c1, p, r1c2, ":r1c2"
    Debug.Print "r: ", , r
    Debug.Print "r2c1:", r2c1, q, r2c2, ":r2c2"
    Debug.Print "================================================================================"

    Interpol2D = r                          'Return the result.

End Function

Figure 1. Test data.

*Table 1. Debug window result.

r1c1:          12            15            18           :r1c2
r:                           16.5 
r2c1:          14            17.5          21           :r2c2
================================================================================

This is a better answer than mine (written before the question was really clear!), and should be marked as correct. — Jonathan R Swift, Apr 21 '18 at 15:13
This is not appropriate comment, but I love this answer and I'm planning for ages to learn vba. I'll try it for sure. — Katarina, Apr 21 '18 at 16:04
Thanks, @Katarina. Many people write horrendously complex Excel formulas that are difficult to read and debug. Very often a quick little bit of VB code can solve the problem very easily. I tried to comment this fairly well so the reader can understand how it works. I hope you find it useful. — Transistor, Apr 21 '18 at 19:05

score 1 · Answer 2 · answered Apr 20 '18 at 14:41

I have a spreadsheet that I wrote years and years ago for interpolating. It generates points that follow the same sort of profile as the 'smoothed line' function when you add a line to an X/Y Scatter Plot.

In the image below, the known data is inputted on the left, and trend values are calculated to give an idea of the gradient at the outer edges of the data. The desired X values are put into the right hand table, and the Y values are calculated accordingly.

On the graph, the Large Orange blobs are the original 'known data', the orange line is the 'smoothed line' generated by excel's graph functions, and the blue dots are the interpolated X/Y values.

You can download the spreadsheet here, but I know not everybody likes downloading unknown files from the internet, so I've included enough information to recreate the file from scratch below.

The formula for the 'trend' boxes are simple. For Cell D5 as shown, it is:

=TREND(D6:D7,,0)

The formulae for the 'Interoplated' boxes are less than simple. For Cell I5 as shown, it is:

=SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},INDEX(D$5:D$13,MATCH(H5,D$5:D$13)+N(IF(1,{-1;0;1;2})))-H5
 )))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},INDEX(E$5:E$13,MATCH(H5,D$5:D$13)+N(IF(1,{-1;0;1;2})))))/2

If you recreate the spreadsheet exactly as in the image, and pay attention to how the formulae link the ranges, you can understand how to expand this for any size of dataset.

I have added a bit more info to my question, will the codes you provided be the same for the type of interpolation that I have to do in that data set? — , Apr 20 '18 at 23:34

Double interpolation for a large set of data in Excel

2 Answers2