3D Interpolation in Excel

Syntax

=INTERPXYZ(xy, z, q, [options])

=INTERPXYZ(xy, z, grid_x, grid_y, [options])


INTERPXYZ is a versatile 3D interpolation function based on natural neighbor algorithm. It can be used to query the value of a surface passing through a set of (x,y,z) data points at an arbitrary (xq,yq) point . It can also be used to map a set of scattered (x,y,z) data points onto a uniform rectilinear grid for the purpose of plotting a 3D surface in Excel.

  • Use =INTERPXYZ(xy, z, q) to interpolate a set of scattered (x,y,z) data points at a query (xq,yq) point.
  • Use =INTERPXYZ(xy,z, grid_x, grid_y) to map set of scattered (x,y,z) data points onto a uniform rectilinear grid.

With optional arguments, you can control the natural neighbor algorithm.

Required Inputs

xy a 2-column array of scattered (x,y) points coordinates.

z a vector of corresponding z values

If using INTERPXYZ for interpolating:

q a two-column array of one or more (xq,yq) point(s) to interpolate at.

If q is a vector of points, run INTERPXYZ as an array formula.

If using INTERPXYZ for gridding:

grid-x a row-vector containing the uniform x-ccordinates for the rectilinear grid.

grid-y a column-vector containing the uniform y-ccordinates for the rectilinear grid.

The x- and y-coordinates vectors must be arranged as shown. The yellow range will hold the interpolated grid z values for each (xi,yi) grid point.

ABCD..
1x0x1xe
2y0
3y1
4
..ye

Optional Inputs

ctrl a set of key/value pairs for algorithmic control as detailed below.

Description of key/value pairs for algorithmic control

KeyPERTURB
Admissible Values (Boolean)True or False
Default ValueTrue
RemarksA True value will slightly perturb the locations of the scattered (x,y) points to aid stability of Voronoi construction. This perturbation has no significant effect on the accuracy of the interpolated data.

KeyNONNEGZ
Admissible Values (Boolean)True or False
Default ValueFalse
RemarksSet to True to force negative interpolated z values to zero.This may be useful to mask roundoff errors during interpolation when the surface passing through the points is known to be nonnegative.

Suppose you have a random set of (x,y,z) data points for some function z=f(x,y). How would you plot the 3D surface in Excel? the short answer is you can't unless you can resample the function at a uniform grid. If the function f(x,y) is unknown, re-sampling at a grid may not be an option. In this example, we will show how you can use INTERPXYZ to interpolate the scattered data set onto a uniform grid for easy plotting. To illustrate the concepts, we will sample the random set from a famous function: the Mexican hat function, a radially symmetric sinc function given by: fx,y=sinx2+y2x2+y2 which has a shape like a Mexican hat.

To generate the scattered data, we use Excel Rand() function to generate random values for x, and y in the interval [0 12]. To preserve the generated random numbers from continuously changing, we must copy them into a new range as numbers only. The table below shows our generated scattered (x,y) points and corresponding z values for the Mexican hat function using Excel's AutoFill feature.

ABC
3random xrandom yz values
40.3971347837.460698663=SIN(SQRT($A4^2+$B4^2))/SQRT($A4^2+$B4^2)
50.0353905761.561260430.64031692
65.6664415333.121434807
Drag down to row 300
......
2998.1396332934.6074124730.007648947
3001.5377101287.4843705840.127912611

We have generated about three hundred points to obtain good coverage for the large [0 12] by [0 12] grid area we plan to use to be able to to show the Mexican hat function.
The steps to generate a grid in Excel are straight forward. we create a uniform row vector for the x-values and a uniform vertical vector for the y-values, as shown below. We chose to create a grid in increment of 0.4 from 0 to 12 for both x and y directions using AutoFill. Note how the x- and y-coordinates are arranged. the yellow shaded area represents the grid (x,y) points at which the z values will be found.

HIJkALAM
300.411.612
40
50.4
6
3211.6
3312

Now to interpolate the scattered (x,y,z) data onto the grid, we evaluate the array formula =INTERPXYZ(A3:B300, C3:C300, I3:AM3, H4:H33) in the yellow range. The first argument A3:B300 is the (x,y) values of the scattered data points, the 2nd argument C3:C300 is the corresponding z values, the 3rd argument I3:AM3 is the generated grid x values, and the 4th argument H4:H33 is the generated grid y values. Excel does the rest for us and correctly computes the interpolated z values at each grid location. The interpolated values are partially shown below.

HIJkALAM
300.411.612
400.9337642590.925546131-0.077545355-0.072129791
50.40.8863574390.899864902-0.077414724-0.075278597
6
3211.6-0.078428067-0.075255129-0.038773976-0.046496635
3312-0.076052838-0.076907303-0.042992068-0.04770666

To generate the surface plot in Excel, we simply highlight the yellow range and insert a surface plot. The plot is shown below and shows only one quarter of the radially symmetric Mexican hat function.

INTERPXYZ implements the natural neighbor interpolation algorithm.

  • Wikipedia, https://en.wikipedia.org/wiki/Natural_neighbor_interpolation.
Question or Comment? Email us:
support @ excel-works.com
ExceLab: Transforming Excel into a Calculus Power House
ExceLab functions and methods are protected by USA Patents 9286286, 9892108, 10114812 and pending.
© 2015-2019, ExcelWorks LLC
Boston, USA