NxINTRPL - Interpolation and Extrapolation

Estimate the function value represented by the (x,y) data set at an intermediate x-value.

Syntax

NxINTRPL(X, Y, target, Method, extrapolate)

X
is the x-component of the input data table (a one-dimensional array of cells (e.g., rows or columns)).
Y
is the y-component (i.e., function) of the input data table (a one-dimensional array of cells (e.g., rows or columns)).
target
is the desired x-value(s) to interpolate for (a single value or a one-dimensional array of cells (e.g., rows or columns)).
Method
is the interpolation method (1 = Forward Flat, 2 = Backward Flat, 3 = Linear, 4 = Cubic Spline).
Value Method
0 Linear (default).
1 Forward Flat.
2 Backward Flat.
3 Cubic Spline.
4 Akima Spline.
5 Steffen's (Monotonic) Spline.
extrapolate
sets whether or not to allow extrapolation (1=Yes, 0=No). If missing, the default is not to allow extrapolation.
Value Extrapolate
0 No (default).
1 Yes.

Remarks

  1. The X and Y array sizes must be identical.
  2. The X-array and Y-array both consist of numerical values. Dates in Excel are internally represented by numbers.
  3. The values in the X-array can be unsorted and may have duplicate values.
  4. In the case where X has duplicate values, NxINTRPL will replace those duplicate values with a single entry, setting the corresponding y-value equal to the average.
  5. The X and/or Y arrays may have missing values (#N/A). In this case, NxINTRPL will remove those entries.
  6. For cubic spline interpolation, we construct a set of natural cubic splines that are twice continuously differentiable functions to yield the least oscillation about the function f, which is interpolated.
  7. The NxINTRPL function is available starting with version 1.66 PARSON.

Examples

Example 1: *Interpolation (ordered X and no missing values)

 
1
2
3
4
5
6
7
8
9
10
11
A B
X Y
0.10 0.01
0.50 0.25
1.00 1.00
1.50 2.25
2.00 4.00
2.20 4.84
2.60 6.76
2.80 7.84
3.00 9.00
4.00 16.00

 

Formula Description (Result)
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,1,0) Forward-flat interpolation (4).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,2,0) Backward-flat interpolation (4.84).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,0,0) Linear interpolation (4.63).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,3,0) Cubic spline interpolation (4.619).

Example 2: *Extrapolation (ordered X and no missing values)

 
1
2
3
4
5
6
7
8
9
10
11
A B
X Y
0.10 0.01
0.50 0.25
1.00 1.00
1.50 2.25
2.00 4.00
2.20 4.84
2.60 6.76
2.80 7.84
3.00 9.00
4.00 16.00

 

Formula Description (Result)
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,1,1) Forward-flat interpolation (4).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,2,1) Backward-flat interpolation (4.84).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,0,1) Linear interpolation (4.63).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,3,1) Cubic spline interpolation (4.619).

Example 3: *Interpolation (un-ordered X and no missing values)

 
1
2
3
4
5
6
7
8
9
10
11
A B
X Y
2 4.00
1.00 1.00
0.50 0.25
1.50 2.25
0.10 0.01
2.20 4.84
2.60 6.76
2.80 7.84
1.00 1.00
3.00 9.00

 

Formula Description (Result)
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,1,0) Forward-flat interpolation (4).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,2,0) Backward-flat interpolation (4.84).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,0,0) Linear interpolation (4.63).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,3,0) Cubic spline interpolation (4.619).

Example 4: *Interpolation (un-ordered X, no duplicates, and with missing values)

 
1
2
3
4
5
6
7
8
9
10
11
A B
X Y
2 4.00
1.00 1.00
0.50 0.25
1.50 #N/A
0.10 0.01
2.20 4.48
2.60 6.76
2.80 7.84
1.00 #N/A
3.00 9.00

 

Formula Description (Result)
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,1,0) Forward-flat interpolation (4).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,2,0) Backward-flat interpolation (4.48).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,0,0) Linear interpolation (4.36).
=NxINTRPL($A$2:$A$11,$B$2:$B$11,2.15,3,0) Cubic spline interpolation (4.335).

Example 5: *Interpolation (un-ordered X, with duplicates, and no missing values)

 
1
2
3
4
5
6
7
8
9
10
11
12
13
A B
X Y
2 4.00
1.00 1.05
1.00 1.00
0.50 0.25
1.50 2.25
1.50 2.10
0.10 0.01
2.20 4.84
2.60 6.76
2.80 7.84
1.00 1.30
3.00 9.00

 

Formula Description (Result)
=NxINTRPL($A$2:$A$13,$B$2:$B$13,2.15,1,0) Forward-flat interpolation (4).
=NxINTRPL($A$2:$A$13,$B$2:$B$13,2.15,2,0) Backward-flat interpolation (4.84).
=NxINTRPL($A$2:$A$13,$B$2:$B$13,2.15,0,0) Linear interpolation (4.63).
=NxINTRPL($A$2:$A$13,$B$2:$B$13,2.15,3,0) Cubic spline interpolation (4.619).

 

Tutorial Video

Files Examples

Related Links

References

  • Kincaid, David; Ward Cheney (2002). Numerical Analysis (3rd edition). Brooks/Cole. ISBN 0-534-38905-8. Chapter 6.
  • Ahlberg, Nielson, and Walsh, The Theory of Splines and Their Applications, 1967.

Comments

Please sign in to leave a comment.

Was this article helpful?
2 out of 2 found this helpful