ExcelBanter - Looking For Interpolation code

There are free utilities for this easy to find via google.

If you're looking to find formulas for 2D estimation,
i'd start with straight lines and then move on to polynomials.
As in the linear case, 2D splines can be built from their 1D
counterparts.

Eg Consider the data set below with lookup values in B1:E1
and A2:A5, and data in the range B2:E5.

0 1 3 5 6 4
1 1 2 4 5 2.95
2 2 4 7 8 5.55
4 3 6 2 3 3.75
5 4 9 8 9 8.5

3 2.5 4.83 4 5 4.29

1. The bilinear estimate at the point (4,3) is 4.75 which
can be obtained by finding the midpoints of the surrounding
2x2 array i.e. the midpoint of (5,4.5) or (5.5,4).

2. For a bicubic use the surrounding 4x4 array of points,
and then apply 1D interpolation at each set of points. You
can do this by filling across and down the formulas below,
to get the values shown in the table above.

B6:=TREND(B2:B5,$A2:$A5^{1,2,3},$A6^{1,2,3})
F2:=TREND(B2:E2,B$1:E$1^{1;2;3},F$1^{1;2;3})

3. To use a bicubic spline, use a weighting matrix designed
to smooth the joins at the knot points. A standard one is
contained in the formulas below:

B6:=SUMPRODUCT((($A6-$A3)/($A4-$A3))^{0,1,2,3}*
{0,-1,2,-1;2,0,-5,3;0,1,4,-3;0,0,-1,1}*B2:B5)/2

F2:=SUMPRODUCT(((F$1-C$1)/(D$1-C$1))^{0;1;2;3}*
{0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1}*B2:E2)/2

Filling across and down gives 4.71.

"ronq" wrote:

I am looking for some to perform bicubic spline, polynomial and/or
other nonlinear interpolation on X, Y , Z data sets. Ideally, I'd
like to interpolation a 2D array of values and possibly better derive
equations to calculate data the data points. Any help would be
appreciated.