POLYFROMROOTS Function for Excel

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

=L_POLYFROMROOTS(roots)

Argument	Description	Example
roots	A vector of roots of the polynomial (may be complex numbers).	{1,-1,0,5}

Description

L_POLYFROMROOTS can be used to create a polynomial from a known set of roots. It returns a row vector of coefficients of the monic polynomial p(x), where monic means that the coefficient of the highest power term is 1: $β_n=1$.

$$p(x) = β_nx^n + β_{n-1}x^{n-1} + … + β_2x^2 + β_1x^1 + β_0$$

The result is the array {β_n=1, β_n-1,...,β₁,β₀}, which is the same type of row vector you would use as the input for the other polynomial functions such as POLYVAL or POLYDER.

Remember is that if your factored polynomial is $x(x+1)(x-2)(x+5)$, the corresponding roots are x = 0, -1, 2, -5. The order of the roots does not matter.

L_POLYFROMROOTS runs the L_POLYMULT function sequentially, and both of these functions allow the roots to be complex numbers.

=LET(
    roots,{0;-1;2;-5},
    L_POLYFROMROOTS(roots)
)

Result: {1, 4, -7, -10, 0}
p(x) = 1x^4 + 4x^3 - 7x^2 - 10x + 0

The roots of a polynomial can be found using L_POLYROOTS. Even though creating a polynomial from known roots has non-educational applications, this function is convenient for generating polynomials for the classroom or for testing root-finding algorithms.

Working with Complex Roots

This function (and the L_POLYMULT function used within) is designed to only use the complex number functions if the roots and subsequent calculations are text values. It uses the ISTEXT function to check if a number if complex, assuming that if it is text, it is a valid complex number.

The other assumption is that the final polynomial p(x) must only contain real value coefficients. Therefore, any imaginary part of the coefficients resulting from numerical error is truncated prior to returning the result (assuming that the imaginary part is very small and only a result of precision or roundoff errors).

Lambda Formula

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

/**
* Create a monic polynomial from a given vector of roots
*/
L_POLYFROMROOTS = LAMBDA(roots,
LET(doc,"https://www.vertex42.com/lambda/polyfromroots.html",
    roots,IF(AND(ROWS(roots)=1,COLUMNS(roots)>1),TRANSPOSE(roots),roots),
    poly,REDUCE(1,SEQUENCE(ROWS(roots)-1),LAMBDA(acc,i,
        IF(i=1,
            IF(OR( ISTEXT(INDEX(roots,1)), ISTEXT(INDEX(roots,2)) ),
                L_POLYMULT(HSTACK(1,IMPRODUCT(-1,INDEX(roots,1))),
                  HSTACK(1,IMPRODUCT(-1,INDEX(roots,2)))),
                L_POLYMULT(HSTACK(1,-INDEX(roots,1)),
                  HSTACK(1,-INDEX(roots,2)))
            ),
            IF(OR(SUM(--ISTEXT(acc))>0,ISTEXT(INDEX(roots,i+1))),
                L_POLYMULT(acc,HSTACK(1,IMPRODUCT(-1,INDEX(roots,i+1)))),
                L_POLYMULT(acc,HSTACK(1,-INDEX(roots,i+1)))
            )
        )
    )),
    realpoly,MAP(poly,LAMBDA(inum,IMREAL(inum))),
    realpoly
));

Named Function for Google Sheets

Name: L_POLYFROMROOTS
Description: Creates a monic polynomial from a vector of roots
Arguments: roots
Function:
[Same as Excel version]

L_POLYFROMROOTS Example

Example

What is the polynomial having the roots 3, 2+SQRT(5)i, and 2-SQRT(5)i

Test: Copy and Paste this LET function into a cell
=LET(
    roots, HSTACK(3, COMPLEX(2,SQRT(5)), COMPLEX(2,-SQRT(5)) ),
    L_POLYFROMROOTS(roots)
)

Result: {1, -7, 21, -27}
p(x) = 1x^3 - 7x^2 + 21x - 27

L_POLYFROMROOTS