≡ ▼
Work in Progress :: Please contact us to report errors, typos, etc.
=L_PPVAL(pp_array, xvec)
ArgumentDescriptionExample
pp_arrayA piecewise polynomial (pp) data structure array[see below]
xvecThe vector of values at which to evaluate the function{0;1;2}

Requires: L_POLYVAL

Download the Template

In the template file, navigate to the Polynomials worksheet to see the L_PPVAL function in action.

Description

A Piecewise Polynomial is a curve that is made from connecting separate polynomial curves end-to-end. Each polynomial is defined between specific bounds, referred to as "break points" in the data structure. Technically, the piecewise polynomial does not need to be continuous, nor does it need to be defined for all values of x. But, this function is not currently robust to gaps in the break points. If an x value is outside the bounds defined by the break points, a #N/A error is returned for that value.

The data structure for this function consists of a single array where the values in the first column are the break points and the other columns are the coefficients of the polynomials in decreasing order of power. Each row is a separate polynomial, but the powers are the same for each column, so the constant term is on the right, the x term is second from right, x2 third from right, etc.

Data Structure for a Piecewise Polynomial in Excel

In this example, p1 is defined on the interval [0,4], p2 on the interval (4,10] and p3 on the interval (10,15]. If x=4, the point will be evaluated using p1.

One important thing to remember when creating a piecewise polynomial is that for this data structure, each individual polynomial is defined as though the left bound starts at x=0. This is why when evaluating the piecewise polynomial function, p1, p2 and p3 in the example are p(x-0), p(x-4) and p(x-10). This only requires a simple shift in the x values prior to fitting a curve. When evaluating the function, L_PPVAL performs this shift automatically.

Lambda Formula

This code for using L_PPVAL 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 for AFE Workbook Module (Excel Labs Add-in)

/**
* Evaluate a Piecewise Polynomial at each value of x
*/
L_PPVAL = LAMBDA(pp_array,xvec,
LET(doc,"https://www.vertex42.com/lambda/ppval.html",
    breaks,INDEX(pp_array,0,1),
    pieces,ROWS(breaks)-1,
    coeffs,DROP(pp_array,-1,1),
    MAP(xvec,LAMBDA(x,IF(OR(xMAX(breaks)),NA(),
        LET(
            row,IF(x=INDEX(breaks,pieces+1,1),pieces,MATCH(x,breaks,1)),
            L_POLYVAL(INDEX(coeffs,row,0),x-INDEX(breaks,row))
        )
    )))
));

Named Function for Google Sheets

Name: L_PPVAL
Description: Evaluate a Piecewise Polynomial at each value of x
Arguments: pp_array, xvec
Function:
[In the works]

L_PPVAL Examples

Example
Evaluate the above piecewise polynomial function for 101 values between 0 and 15.
Test: Copy and Paste this LET function into a cell
=LET(
    xvec, L_LINSPACE(0,15,101),
    pp_array, HSTACK({0;4;10;15},{0,2,-1,1,10; 0,0,-5,0,126; -1,5,1,4,-54}),
    L_PPVAL(pp_array,xvec)
)

See Also

LINSPACE, PINTERP, POLYVAL, CSPLINE, NSPLINE

Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.