2D Interpolation in Excel

Syntax

=INTERPXY(x, y, q, [options])


INTERPXY is a versatile 2D interpolation function based on splines.

Use INTERPXY to interpolate from a set of (x,y) data points at an arbitrary point.
Use INTERPXY to map a scattered (x,y) data points onto a uniform grid for easy plotting in Excel.

With optional arguments, you can control the interpolating spline properties.

Required Inputs

x x-coordinates of the scattered (x,y) points.

y corresponding y values.

q query point to interpolate at.

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

Optional Inputs

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

Description of key/value pairs for algorithmic control

KeyORDER
Admissible Values (Integer)1 or 3 (linear or cubic)
Default Value3
KeysISLOPE, ESLOPE
Admissible Valuesreal number
Default ValueUnconstrained
Remarks
  • If defined, the spline curve first derivative will match the supplied value.
  • Cannot be imposed with a linear spline or with PERIOIDC option enabled.
KeyPERIODIC
Admissible Values (Boolean)True or False
Default ValueFalse
Remarks
  • The y-values at the end points must be equal if PERIOIDC is set to True.
  • ISLOPE, ESLOPE constraints are ignored
KeySFACTOR
Admissible Values (real)≥ 0
Default Value0
Remarks
  • A zero value will compute an exact interpolating spline passing through the data points. A large value will compute a least-squares smooth fitting curve.
  • The smoothing factor has a significant effect on the computed integral value.

w strictly-positive corresponding set of weights for the (x,y) data points. Default value is unity.

In this example, we sample the function f(x) = x sin(x2) +1 at random points, and then reconstruct the function at a uniform grid for easy plotting in Excel.

Using Excel's RAND() function we generate a vector of random numbers in the range [0 to 3]. To preserve the generated random numbers from continuously changing, we copy them into range A4:A28 as numbers only. Using AutoFill, we generate corresponding y values in range B4:B28 using the formula =A4*SIN(A4^2)+1. Next we generate a uniform grid for x values in range D4:D28 from 0 to 3 in increment of 0.125.
Finally, we evaluate =INTERPXY(A4:A28, B4:B28, D4:D28) as array formula in range E4:E28 to interpolate the scattered data onto the uniform grid. The numerical values are shown below.

ABDE
3scattered xy valuesunifrom xinterpolated y
41.7530290641.11995669501.001328093
50.5681162281.1801959580.1251.002172559
61.4402162582.261535310.251.01561687
72.28511224-0.9950449840.3751.052557644
81.0150034891.8702827150.51.123729162
91.2171312112.2122718840.6251.238002939
102.429384636 0.0960143920.751.400044332
111.0611136721.957848280.8751.606510771
122.8282389123.7985780211.84148167
131.5909856611.9118897691.1252.073236147
141.4545783372.2438482661.252.249813639
151.6393591841.7191023081.3752.305014902
162.385193008-0.3350239141.52.166955797
170.9550150971.7551902261.6251.78044105
180.327442091.0350405651.751.138439619
191.5796865871.9511977011.8750.303724928
200.4141157311.0706698822-0.514903922
210.9138842081.6775722392.125-1.068933993
220.2653055721.018658662.25-1.109851826
231.6194838311.8030933652.375-0.422949211
241.6104144341.8392127222.50.926799955
252.6588024492.8812732332.6252.50361038
262.310702177-0.8712467592.753.629673183
272.799686453.7993397632.8753.616506327
280.7297571391.37051810431.774219949

We can also generate column E using AutoFill instead of evaluating INTERPXY as array formula. However, if we use the AutoFill feature, we must lock arguments 1 and 2 so Excel does not increment them during the AutoFill by using the formula =INTERPXY(A$4:A$28, B$4:B$28, D4). The array formula option is computationally more efficient.

The Figure below shows the interpolated y values plotted against the generated uniform x values. This plot is easily generated in Excel by highlighting columns D and E and inserting a scatter plot.

INTERPXY computes the interpolation by fitting a spline model to the data.

  • Paul Dierckx. Curve and Surface Fitting with Splines. Numerical Mathematics and Scientific Computation. Oxford University Press, (1995).
  • Carl de Boor. A Practical Guide to Splines (Applied Mathematical Sciences). Springer, (2001).
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