Bilinear interpolation from a continuous Z = f(X,Y) data series

**ice00monster** · 04-15-2019, 01:52 PM

Hello.

I'd like to have some clue if a VBA code is available for bilinear interpolation of a continuous list; ie. given a continuous (not surface table) list of X, Y, and Z = f(X,Y) data, I would then be able to interpolate if I input a value of X and Y.

The closest thing that I found in the forums related to my problem is this:
www(dot)excelforum.com/excel-general/794506-3d-interpolation-for-surface-chart.html

Your help is greatly appreciated. Thanks!

**Dal123** · 04-15-2019, 02:19 PM

Let me guess, survey data?

I don't think anything's out there to download but I'd say you could knock something up with vba.

**MrShorty** · 04-15-2019, 03:04 PM

No VBA used in this one: https://www.excelforum.com/excel-gen...ml#post4300787
nor this one: https://www.excelforum.com/excel-for...ml#post4757864
See if either of those will help you put together a bilinear interpolation algorithm for your data.

**ice00monster** · 04-16-2019, 08:45 AM

Hi, thanks for replying.

Actually I am quite able to do those things already. I have a long list of X, Y, and Z data (not tabular as with the common bilinear Excel schemes) and I need to interpolate those points.

**MrShorty** · 04-16-2019, 09:05 AM

That would seem very similar to the scenario described in the first link I gave, where the OP had a list of X, Y, Z data. As noted there, I suggested that the first step is to convert from a list to a table using a pivot table or similar. Are you saying that you are not allowed to change the data arrangement -- that you must perform the interpolation on the list arranged data? I have said elsewhere that the lookup step is the most difficult part of an interpolation algorithm in Excel. With bilinear interpolation, I find that the lookup step is even more difficult if the data are in a list. I would want to be sure that arranging the data in a table is absolutely forbidden before trying it.

**ice00monster** · 04-17-2019, 11:55 PM

Yeah, it would be strongly preferred to not rearrange the data. Is this even possible?

What I see is that people do this so-called 'nearest neighbor algorithm' instead on the datasets

**MrShorty** · 04-18-2019, 12:31 AM

Possible? I am confident that it is possible. As I noted, a lot of the difficulty in interpolation algorithms is the lookup step. With bilinear interpolation, you need to find 4 adjacent points. With your data in a list like you describe, it is more difficult to find the four adjacent points. Why do you prefer not to rearrange the data? I have always felt that it was so much easier to rearrange the data that I have never really tried while keeping the data in a list. Do you have a sample data set for testing?

A Nearest neighbor algorithm would be significantly easier.With a nearest neighbor algorithm, you need only find one point. Again, I think this is easier if the data are in a table rather than a list. With the data in a list, I envision either a two step pair of lookup functions or combine the x&y data into a helper column in a way that I can use the helper column to find the "nearest" x,y,z point.

**AJMACENG** · 02-25-2021, 02:23 PM

Hi ice00monster,

I see this is a old thread, and I was not completely happy with the solutions given as they used multiple cells or VBA. So I figured I'd post my solution here, does the bilinear interpolation in a single cell and doesn't need VBA.

The formula in cell C18 of attached image here: =(TREND(OFFSET($C$6:$G$10,MATCH($B18,$B$6:$B$10,1)-1,MATCH(C$17,$C$5:$G$5,1)-1,2,1),OFFSET($B$6:$B$10,MATCH($B18,$B$6:$B$10,1)-1,0,2,1),$B18,TRUE)*(OFFSET($C$5:$G$5,0,MATCH(C$17,$C$5:$G$5,1),1,1)-C$17)+TREND(OFFSET($C$6:$G$10,MATCH($B18,$B$6:$B$10,1)-1,MATCH(C$17,$C$5:$G$5,1),2,1),OFFSET($B$6:$B$10,MATCH($B18,$B$6:$B$10,1)-1,0,2,1),$B18,TRUE)*(C$17-OFFSET($C$5:$G$5,0,MATCH(C$17,$C$5:$G$5,1)-1,1,1)))/(OFFSET($C$5:$G$5,0,MATCH(C$17,$C$5:$G$5,1),1,1)-OFFSET($C$5:$G$5,0,MATCH(C$17,$C$5:$G$5,1)-1,1,1))

Attachment 720759

**ice00monster** · 02-26-2021, 04:25 PM

Originally Posted by AJMACENG

Hi ice00monster,

I see this is a old thread, and I was not completely happy with the solutions given as they used multiple cells or VBA. So I figured I'd post my solution here, does the bilinear interpolation in a single cell and doesn't need VBA.

The formula in cell C18 of attached image here: =(TREND(OFFSET($C$6:$G$10,MATCH($B18,$B$6:$B$10,1)-1,MATCH(C$17,$C$5:$G$5,1)-1,2,1),OFFSET($B$6:$B$10,MATCH($B18,$B$6:$B$10,1)-1,0,2,1),$B18,TRUE)*(OFFSET($C$5:$G$5,0,MATCH(C$17,$C$5:$G$5,1),1,1)-C$17)+TREND(OFFSET($C$6:$G$10,MATCH($B18,$B$6:$B$10,1)-1,MATCH(C$17,$C$5:$G$5,1),2,1),OFFSET($B$6:$B$10,MATCH($B18,$B$6:$B$10,1)-1,0,2,1),$B18,TRUE)*(C$17-OFFSET($C$5:$G$5,0,MATCH(C$17,$C$5:$G$5,1)-1,1,1)))/(OFFSET($C$5:$G$5,0,MATCH(C$17,$C$5:$G$5,1),1,1)-OFFSET($C$5:$G$5,0,MATCH(C$17,$C$5:$G$5,1)-1,1,1))

Attachment 720759

Hi!

Thanks for replying. I actually am still using such formulae in my studies, and I would be interested in seeing how yours work. Though it would be helpful if you attach a spreadsheet file, the attachment in your post does not seem to work

**AJMACENG** · 02-26-2021, 04:59 PM

Hi in re-reading this, I see you were looking for all data in a single column, so this might not be what you'd need.
However I think you can reorganize that the formula so that horizontal look-up is vertical, as long as data is sorted in increasing order and grouped by x, and y.

Here's the spreadsheet link (hopefully). www(dot)excelforum(dot)com/attachment.php?attachmentid=720929&stc=1&d=1614371500

In fact, it doesn't seem to hard to take it one step further and look up between tables f(x,y,z)

Hope this helps. First time posting on a forum.