estimate the value of the function represented by (x,y) data set at an intermediate x-value.
Syntax
INTERPOLATE(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 |
1 |
Forward Flat |
2 |
Backward Flat |
3 |
Linear (default) |
4 |
Cubic 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
Examples
Example 1: *Interpolation (ordered X and no missing values)
|
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) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,1,0) |
Forward-flat interpolation (4) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,2,0) |
Backward-flat interpolation (4.84) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,3,0) |
Linear interpolation (4.63) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,4,0) |
Cubic spline interpolation (4.619) |
Example 2: *Extrapolation (ordered X and no missing values)
|
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) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,1,1) |
Forward-flat interpolation (4) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,2,1) |
Backward-flat interpolation (4.84) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,3,1) |
Linear interpolation (4.63) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,4,1) |
Cubic spline interpolation (4.619) |
Example 3: *Interpolation (un-ordered X and no missing values)
|
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) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,1,0) |
Forward-flat interpolation (4) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,2,0) |
Backward-flat interpolation (4.84) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,3,0) |
Linear interpolation (4.63) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,4,0) |
Cubic spline interpolation (4.619) |
Example 4: *Interpolation (un-ordered X, no duplicates, and with missing values)
|
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) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,1,0) |
Forward-flat interpolation (4) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,2,0) |
Backward-flat interpolation (4.48) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,3,0) |
Linear interpolation (4.36) |
|
=INTERPOLATE($A$2:$A$11,$B$2:$B$11,2.15,4,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) |
|
=INTERPOLATE($A$2:$A$13,$B$2:$B$13,2.15,1,0) |
Forward-flat interpolation (4) |
|
=INTERPOLATE($A$2:$A$13,$B$2:$B$13,2.15,2,0) |
Backward-flat interpolation (4.84) |
|
=INTERPOLATE($A$2:$A$13,$B$2:$B$13,2.15,3,0) |
Linear interpolation (4.63) |
|
=INTERPOLATE($A$2:$A$13,$B$2:$B$13,2.15,4,0) |
Cubic spline interpolation (4.619) |
Files Examples
0 Comments