POLYVAL Function in Excel

Work in Progress :: Please contact us to report errors, typos, etc.

=L_POLYVAL(coeffs, x)

Argument	Description	Example
coeffs	Row of coefficients in decreasing power order	{5,0,3.2}
x	A single value or a vector of x values	{1;2;3}

Description

L_POLYVAL evaluates the nth-degree polynomial p(x) for each value in vector x. The polynomial is defined by a 1x(n-1) row vector of constant coefficients in order of decreasing power {βn, βn-1, ... β2, β1, β0} corresponding to {x^n, x^(n-1), ... x^2, x, 1}.

p(x) = β_n*x^n + β_n-1*x^(n-1) + … + β₂*x^2 + β₁*x^1 + β₀

Use LINEST or L_POLYFIT to find the coefficients for a polynomial curve fit.

When n=1 (linear), the FORECAST.LINEAR function in Excel can be used to evaluate p(x) for given values of x:

FORECAST.LINEAR(x,ys,xs) = L_POLYVAL( L_POLYFIT(xs,ys,1), x)

When n>1, the TREND function in Excel can evaluate p(x) for given values of x, but you need to calculate the powers like with the LINEST function.

L_POLYVAL( L_POLYFIT(xs,ys,n), x) = TREND(ys,xs^SEQUENCE(1,n,1),x^SEQUENCE(1,n,1))

3/5/2024 - L_POLYVAL has been updated to work with complex numbers in x. It only uses the imaginary number functions if x is a text value (it assumes that a text value is a valid complex number).

Lambda Formula

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

/**
* Evaluates a polynomial defined by a row vector of constant coefficients for each value in x.
*/
L_POLYVAL = LAMBDA(coeffs,x,
LET(doc,"https://www.vertex42.com/lambda/polyval.html",
    coeffs,IF(ROWS(coeffs)>1,TRANSPOSE(coeffs),coeffs),
    n,COLUMNS(coeffs)-1,
    IF(OR(ISTEXT(x)),
        LET(
            matrix,MAKEARRAY(ROWS(x),n+1,LAMBDA(i,j,
                SWITCH(n-(j-1),
                0,INDEX(coeffs,1,j),
                1,IMPRODUCT(INDEX(coeffs,1,j),INDEX(x,i)),
                2,IMPRODUCT(INDEX(coeffs,1,j),INDEX(x,i),INDEX(x,i)),
                IMPRODUCT(INDEX(coeffs,1,j),IMPOWER(INDEX(x,i),n-(j-1)))
                )
            )),
            BYROW(matrix,LAMBDA(row,LET(sum,IMSUM(row),IF(IMAGINARY(sum)=0,IMREAL(sum),sum))))
        ),
        LET(
            X_mat,IF(n=0,SEQUENCE(ROWS(x),1,1,0),
                HSTACK(x^SEQUENCE(1,n,n,-1),SEQUENCE(ROWS(x),1,1,0))
            ),
            BYROW(coeffs*X_mat,LAMBDA(row,SUM(row)))
        )
    )
));

Named Function for Google Sheets

Name: L_POLYVAL
Description: Evaluate a polynomial p(x) given the coefficients and x values
Arguments: coeffs, x (see above for descriptions and example values)
Function:

=LET(doc,"https://www.vertex42.com/lambda/polyval.html",
  coeffs,IF(ROWS(coeffs)>1,TRANSPOSE(coeffs),coeffs),
  n,COLUMNS(coeffs)-1,
  IF(OR(ISTEXT(x)),
    LET(
      matrix,MAKEARRAY(ROWS(x),n+1,LAMBDA(i,j,
      SWITCH(n-(j-1),
        0,INDEX(coeffs,1,j),
        1,IMPRODUCT(INDEX(coeffs,1,j),INDEX(x,i)),
        2,IMPRODUCT(INDEX(coeffs,1,j),INDEX(x,i),INDEX(x,i)),
        IMPRODUCT(INDEX(coeffs,1,j),IMPOWER(INDEX(x,i),n-(j-1)))
      ))),
      BYROW(matrix,LAMBDA(row,LET(sum,IMSUM(row),IF(IMAGINARY(sum)=0,IMREAL(sum),sum))))
    ),
    LET(
      X_mat,IF(n=0,ARRAYFORMULA(SEQUENCE(ROWS(x),1,1,0)),
        ARRAYFORMULA(HSTACK(x^SEQUENCE(1,n,n,-1),SEQUENCE(ROWS(x),1,1,0)))
      ),
      BYROW(ARRAYFORMULA(coeffs*X_mat),LAMBDA(row,SUM(row)))
    )
))

Function Code Notes

The complexity in this function comes from handling the case where x is a vector instead of just a single value.

BYROW is used to sum coeffs*X_mat by row because SUM would otherwise return just a single value.

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_POLYVAL Examples

Example 1

Evaluate the polynomial function p(x) = 4*x^3 - 3*x + 5 for values of x between 1 and 10. Note that the coefficient for the x^2 term is zero (it must be included in the coeffs vector).

Test: Copy and Paste this LET function into a cell
=LET(
    coeffs, {4,0,-3,5},
    xvalues, L_LINSPACE(1,10,5),
    L_POLYVAL(coeffs,xvalues)
)

Result: {6; 132.5625; 654; 1843.6875; 3975}

Example 2 (Complex Roots)

Evalute p(x)=x^2+2x+3 where x is a vector of complex roots returned from the quadratic formula. The roots are -1±SQRT(2)i.

Test: Copy and Paste this LET function into a cell
=LET(
    coeffs, {1,2,3}
    x, {"-1+1.4142135623731i";"-1-1.4142135623731i"}
    IM_POLYVAL(coeffs,x)
)
Result: {-1.9984E-14;-1.9984E-14}

Note that the resulting values are not exactly zero. This is not only due to the fact that our input was not exact. =IM_POLYVAL(coeffs,IM_QUADRATIC(coeffs)) will produce the same result due to truncation and rounding errors associated with the use of complex number functions. Complex number functions are only used if x is a text value (i.e. the way complex numbers are stored in Excel).

Change History

4/09/2024 - v1.0.11 -Updated to work with p(x)=c (polynomial is just a constant)

3/05/2024 - v1.0.6 - Updated to work with complex numbers in x

2/10/2024 - v1.0.2 - Updated the X_mat calculation so that it will return an error if values are complex

L_POLYVAL