≡ ▼
```=L_PINTERP(x, known_xs, known_ys, n)
```
ArgumentDescriptionExample
xValue or vector of values to be interpolated2
known_xsVector of known x values{1;2;3;4;5}
known_ysVector of known y values{0.9;0.14;-0.76}
nDegree of the polynomial3

Required: L_POLYVAL, L_POLYFIT

Description

L_PINTERP uses polynomial interpolation between the n+1 nearest points where n is the degree of the polynomial. The nearest points are found by first calculating the square of the distance from x as (x-xs)^2 for all points in known_xs. n+1 is the minimum number of points for exactly fitting a polynomial of degree n.

L_PINTERP uses the functions L_POLYVAL and L_POLYFIT on the subset of n+1 points to return the interpolated value.

The known (x,y) pairs do not need to be sorted because the function sorts the table after calculating the distances. x can be a single value or a vector. If n=1 (linear), then PINTERP should return the same results as LINTERP.

For the sake of this example, the known_ys values are calculated using COS(x). But, the purpose of interpolation is to find values based only on given data rather than knowing that actual formula for y.

COS(x) is shown in the example graph only to illustrate how closely the cubic (n=3) polynomial interpolation fits the actual curve.

Polynomial interpolation and other types of interpolation can be useful for obtaining intermediate values when you only have a table of data rather than knowing the actual mathematical relationship between x and y.

Polynomial interpolation should not be confused with regression (statistically fitting a polynomial). If your data table contains statistical data with significant variation or scatter, polynomial interpolation may not be appropriate (see the example below). Linear regression tends to have a smoothing effect, but polynomial interpolation exactly fits a polynomial to the n+1 closest points.

Lambda Formula

This code for using L_PINTERP in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code to Create Function via the Name Manager

```Name: L_PINTERP
Comment: Polynomial interpolation between the n+1 closest points
Refers To:

=LAMBDA(xs,known_xs,known_ys,n,
LET(doc,"https://www.vertex42.com/lambda/pinterp.html",
xs,IF(AND(ROWS(xs)=1,COLUMNS(xs)>1),TRANSPOSE(xs),xs),
known_xs,IF(AND(ROWS(known_xs)=1,COLUMNS(known_xs)>1),TRANSPOSE(known_xs),known_xs),
known_ys,IF(AND(ROWS(known_ys)=1,COLUMNS(known_ys)>1),TRANSPOSE(known_ys),known_ys),
BYROW(xs,LAMBDA(x,
LET(tab,TAKE(SORT(HSTACK((known_xs-x)^2,known_xs,known_ys)),n+1),
L_POLYVAL(L_POLYFIT(INDEX(tab,0,2),INDEX(tab,0,3),n),x)
)
))
))
```

Code for AFE Workbook Module (Excel Labs Add-in)

```/**
* Polynomial interpolation between the n+1 closest points based on distance (x0-x)^2
*/
L_PINTERP = LAMBDA(xs,known_xs,known_ys,n,
LET(doc,"https://www.vertex42.com/lambda/pinterp.html",
xs,IF(AND(ROWS(xs)=1,COLUMNS(xs)>1),TRANSPOSE(xs),xs),
known_xs,IF(AND(ROWS(known_xs)=1,COLUMNS(known_xs)>1),TRANSPOSE(known_xs),known_xs),
known_ys,IF(AND(ROWS(known_ys)=1,COLUMNS(known_ys)>1),TRANSPOSE(known_ys),known_ys),
BYROW(xs,LAMBDA(x,
LET(tab,TAKE(SORT(HSTACK((known_xs-x)^2,known_xs,known_ys)),n+1),
xo,INDEX(tab,1,2),
L_POLYVAL(L_POLYFIT(INDEX(tab,0,2)-xo,INDEX(tab,0,3),n),x-xo)
)
))
));
```

```Name: L_PINTERP
Description: Polynomial interpolation between the n+1 closest points
Arguments: xs, known_xs, known_ys, n
Function:

LET(doc,"https://www.vertex42.com/lambda/pinterp.html",
xs,IF(AND(ROWS(xs)=1,COLUMNS(xs)>1),TRANSPOSE(xs),xs),
known_xs,IF(AND(ROWS(known_xs)=1,COLUMNS(known_xs)>1),TRANSPOSE(known_xs),known_xs),
known_ys,IF(AND(ROWS(known_ys)=1,COLUMNS(known_ys)>1),TRANSPOSE(known_ys),known_ys),
BYROW(xs,LAMBDA(x,
LET(tab,CHOOSEROWS(SORT(HSTACK((known_xs-x)^2,known_xs,known_ys)),SEQUENCE(n+1)),
xo,INDEX(tab,1,2),
ARRAYFORMULA(L_POLYVAL(L_POLYFIT(INDEX(tab,0,2)-xo,INDEX(tab,0,3),n),x-xo))
)
))
)
```

Google Sheets doesn't have a TAKE function, so the GS formula uses CHOOSEROWS.

Warning
These functions are not compatible between Excel and Google Sheets. When using these functions, you will not be able to convert the Excel file to Google Sheets or vice versa without problems.

L_PINTERP Examples

Example 1
Generate the values shown in the example above.
```Test: Copy and Paste this LET function into a cell
=LET(
x, L_LINSPACE(1,8,30),
known_xs, L_SE(1,8),
known_ys, COS(known_xs),
n, 3,
L_PINTERP(x,known_xs,known_ys,n)
)

Result: {0.54;0.303;0.064;-0.168;-0.387;-0.584;-0.752;-0.885;-0.974;...}
```
Example 2
Using interpolation to fit random or scattered data could lead to significant errors. In this example, the y values are generated using the RANDARRAY function (random values between 0 and 1).
```Test: Copy and Paste this LET function into a cell
=LET(
x, L_LINSPACE(1,10,31),
known_xs, L_LINSPACE(1,10,11),
known_ys, RANDARRAY(11,1),
n, 3,
L_PINTERP(x,known_xs,known_ys,n)
)

Result: [variable because of RAND]
```

Using polynomial interpolation generally assumes that function is continuous but unknown. The actual data in this case, being random, would definitely not follow the pattern assumed by the interpolated points. Be careful when using interpolation with random or scattered data.

On the other hand, if this sample data did truly represent a continuous curve, then you can see how polynomial interpolation might approximate values between the various points. Cubic polynomial interpolation (n=3) uses only the 4 nearest points, so the the result is very different from using a single cubic polynomial fit of the entire data set.

Example 3: Numeric Stability at Large X
Polynomial fitting using least squares can be numerically unstable at large values of x. This means that we have rounding and/or precision error within the algorithm. Using POLYVAL(POLYFIT(xs,ys,n),xs) when we have n+1 points should return the exact values for ys that we started with. Here is an example:
```Test: Copy and Paste this LET function into a cell
=LET(
n, 3,
xs, L_LINSPACE(1000,1000+PI(),n+1),
ys, COS(xs),
L_POLYVAL(L_POLYFIT(xs,ys,n),xs)
)

xs: {1000; 1001.047; 1002.094; 1003.142}
ys: {0.562; -0.435; -0.997; -0.562}
Result:
yp: {0.59;  -0.519; -0.913; -0.59}
```
yp are the values returned from L_POLYVAL(L_POLYFIT()) and they should be exactly the same as ys. To help reduce this problem, we can introduce a shift in the x values prior to doing the fit, which is the same thing we do with piecewise polynomials. This shifts the curve to start at x=0 prior to fitting:
```Test: Copy and Paste this LET function into a cell
=LET(
n, 3,
xs, L_LINSPACE(1000,1000+2*PI(),n+1),
xo,INDEX(xs,1),
ys, COS(xs),
L_POLYVAL(L_POLYFIT(xs-xo,ys,n),xs-xo)
)

Result: {0.562;-0.435;-0.997;-0.562}
```
This example demonstrated the error using L_POLYVAL(L_POLYFIT()) because this same procedure is used within PINTERP. PINTERP has been updated with the shift in x as of version 1.0.7 of the lambda library.