PPVAL

=PPVAL(pp_array, xvec)

Requires: POLYVAL

Download the Template

In the template file, navigate to the Polynomials worksheet to see the 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, x² third from right, etc.

In this example, p₁ is defined on the interval [0,4], p₂ on the interval (4,10] and p₃ on the interval (10,15]. If x=4, the point will be evaluated using p₁.

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, p₁, p₂ and p₃ 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, PPVAL performs this shift automatically.

Lambda Formula

This code for using 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.

/**
* Evaluate a Piecewise Polynomial at each value of x
*/
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)),
            POLYVAL(INDEX(coeffs,row,0),x-INDEX(breaks,row))
        )
    )))
));

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

PPVAL Examples

Test: Copy and Paste this LET function into a cell
=LET(
    xvec, 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}),
    PPVAL(pp_array,xvec)
)

See Also

LINSPACE, PINTERP, POLYVAL, CSPLINE, NSPLINE