Find y value with f(x,y) and x value using double interpolation in 2d table

**dave8635357** · 10-25-2014, 09:20 PM

Hi,

I have a chart which looks like this:

x\ y 0 1 2
0 0.01 0.05 0.10
1 0.02 0.08 0.11 <- f(x,y) values
2 0.04 0.10 0.14

In reality it is much bigger, but just a sample. I have a x value and a f(x,y) value and I would like to find the corresponding y value using interpolation.

For example... if I have x=1.5, f(x,y)=0.5, what is y=?

I have found double/bilinear/bicubic interpolation macros on the internet for finding f(x,y) with x and y, just need it in reverse now.

Dave

**misrasomendra** · 10-26-2014, 04:44 AM

Hi Dave,

Well not sure, but see the file, with Formula solution in it.

**dave8635357** · 10-26-2014, 06:02 AM

Hi misrasomendra,

Thank you for your help. However in your spreadsheet, there is no interpolation, excel matches the nearest value in the table. For example you provide, x=0.9, f(x,y)=0.09, and excel finds y=1, but if you interpolate properly it should work out to be 1.406.

Dave

**MrShorty** · 10-26-2014, 10:01 AM

How well have you understood the solutions you have come across? I would expect that the solution to your double interpolation should be very similar to the solutions you have come across. I expect they look something like:

1) Using linear interpolation, create a new array or block of cells that tabulates y and f(x,y) at new x.
2) From this new block of cells, interpolate new f(x,y) from newy.

In your case, I would expect step 1 to be the same. The only difference in step 2 is that you would be looking for newy at newf(x,y). If you have understood the solutions you have found, then it should be fairly easy to modify one of them to get the result you need. If you have not understood them, then I would suggest first understanding how they perform step 1 ("unilinear" interpolation).

FWIW, I tend to think that this will be easiest in QuattroPro because you can simply use its built in @LINTERP() function to perform each unilinear interpolation.