1. Help center
  2. Reference Manual
  3. Data Fitting

NxINTRPL - Interpolation and Extrapolation

Avatar
Mohamad
Follow

Estimate the value of the function represented by (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 to not 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).

 

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.

Related articles

Comments

Please sign in to leave a comment.

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