3D interpolation to estimate z-value

[andisch]

Hi!

I am trying to challenge myself to learn new features of Excel, but have into some difficulties. Would appreciate any help on this matter.

Imagine a city with 100's of buildings. I know the x and y-coordinate along witht the height (z-value) of a set of lets say 10 skyscrapers. They are not placed in a grid, and the x/y-values therefore seem random. I would like to be able to plot a random x/y-coordinate and get the corresponding height (z-value), to estimate the best "fitting" height of a new building based on the nearest skyscrapers.

I am basically trying to find the z-value of a any given x/y-coordinate, based on the nearest known points - see the illustration below. I know the coordinates (x,y and z) to every black dots in the figure. Based on these values, i want to estimate the corresponding z-value of a given x/y-coordinate. Ideally, i would like to be able to plot the green surfaces in a 3D plot/graph along with the red point (with the correct z-value). From what i have been able to find so far, it seems quite difficult in excel - do any of you have any great ideas?

xyz plot.png

I have attached an example file.

Thanks in advance!

[MrShorty]

Bilinear interpolation can be a challenging programming problem to implement. Most of the time, we have a more "regular" grid of values, in your case, they are less regular. In general, the overall algorithm involves:

1) Finding the four points that define the quadrilateral containing the solution.
2) Entering those four points into the interpolation formula: https://en.wikipedia.org/wiki/Biline..._interpolation

As with most interpolation problems in a spreadsheet, the hardest part is the "lookup step" to find the four boundary points. With an irregular grid like you have, this can be even more complicated. How would you go about choosing the four boundary points for a given x,y,z point? For the example point, would you pick buildings, 1,2,5, and 7, or would you pick different points?

[bsalv]

don't think too complex.
461.3 m with the 3 nearest buildings, 516.5 looking at all the buildings

[andisch]

Thanks both of you.

You seem to have different approaches, using either 3 or 4 nearest points to determine the height.

As MrShorty explains, the most difficult part is probably determining which points to use. I was initially going to calculate the length in x/y-plane from the given point to all the known points, and use the 3 buildings with the shortest distance - thereby getting the nearest points. However, this does not require the given point to actually be placed on the surface generated within the nearest points.

Do you have any idea on how to determine which points to actually do the interpolation between? Idieally, i would like a mesh/surface generated by triangles as seen in the image of my original post, but i am not quite sure if its even possible.

[bsalv]

You seem to have different approaches, using either 3 or 4 nearest points to determine the height.

no, 3 points is an exact match, >3 points it's estimate of the least squares-method.