# Polynomial trend equation and forecast

**trend line**is polynomial. However, keep in mind that the polynomial trend is intended to describe the current data set and calculate the values within it. It is not intended for making predictions at all. See more about the different types of trendlines you can create in Excel:

The charts above show polynomial trendlines for the same data with a degree of **2** - the minimum degree of a polynomial trendline in Excel - and **4**.

The **degree of the polynomial** determines the maximum number of extrema - local minima or maxima of the curve:

- A polynomial trendline of the second degree (also known as a
**Quadratic**polynomial trend line) can describe only one maximum or minimum (one hill or valley). - A polynomial trendline of the third degree (also known as a
**Cubic**polynomial trend line) has one or two extrema.

The maximum degree of the polynomial trendline that can be displayed in an Excel chart is **6**:

The **degree of the polynomial** is specified in the **Order** field - see more about the polynomial trendline in Excel.

Excel uses the **least squares** method to fit a polyline for the data points.

For a trend line, two sets of variables are required:

- an independent, predictor, or explanatory variable
(named as*x**known_x's*), - a dependent or response variable
(named as*y**known_y's*).

## Polynomial trendline equation and formulas

The polynomial trendline is a curved line with the function:

*y = a _{n}*x^{n} + ... + a_{3}*x^{3} + a_{2}*x^{2} + a_{1}*x + b*

where:

, ...,*a*_{n},*a*_{3},*a*_{2}and*a*_{1}are calculated parameters of the function (also named function*b**coefficients*or*constants*) that describe the relationship betweenand**x**,*y*is the*n***degree of the polynomial**(in Excel, the**order**of a polynomial). Excel works with polynomial orders from**2**to^{nd}**6**.^{th}

** Note**: Excel uses a linear model: the polynomial function

**is transformed to a linear function using a change of variables. After changing the variables to**

*y = a*_{n}*x^{n}+ ... + a_{3}*x^{3}+ a_{2}*x^{2}+ a_{1}*x + b**X**, the calculations are completely similar to the linear case

_{i}=*x*^{i}**. Now the variable**

*y = a**X_{n}**X*_{n}+ ... + a_{3}**X*_{3}+ a_{2}_{2}+ a_{1}*x + b*does not depend on one variable*

**y****in**

*x***different degrees but on**

*n***independent variables**

*n***X**.

_{i}Excel offers the following functions to calculate the trend parameters:

**Quadratic (2 ^{nd} order or 2^{nd} degree) polynomial trendline**

- Equation:
*y = a*_{2}*x^{2}+ a_{1*}x + b *a*_{2}: = INDEX (LINEST (known_y's, known_x's ^ {1,2}), 1)*a*_{1}: = INDEX (LINEST (known_y's, known_x's ^ {1,2}), 1, 2)*b: = INDEX (LINEST (known_y's, known_x's ^ {1,2}), 1, 3)*

**Cubic (3 ^{rd} order or 3^{rd} degree) polynomial trendline**

- Equation:
*y = a*_{3}*x^{3}+ a_{2}*x^{2}+ a_{1}*x + b *a*_{3}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3}), 1)*a*_{2}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3}), 1, 2)*a*_{1}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3}), 1, 3)*b: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3}), 1, 4)*

**The 4 ^{th} order or 4^{th} degree polynomial trendline**

- Equation:
*y = a*_{4}*x^{4}+ a_{3}*x^{3}+ a_{2}*x^{2}+ a_{1}*x + b *a*_{4}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4}), 1)*a*_{3}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4}), 1, 2)*a*_{2}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4}), 1, 3)*a*_{1}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4}), 1, 4)*b: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4}), 1, 5)*

**The 6 ^{th} order or 6^{th} degree polynomial trendline**

- Equation:
*y = a*_{6}*x^{6}+ a_{5}*x^{5}+ a_{4}*x^{4}+ a_{3}*x^{3}+ a_{2}*x^{2}+ a_{1}*x + b *a*_{6}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4,5,6}), 1)*a*_{5}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4,5,6}), 1, 2)*a*_{4}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4,5,6}), 1, 3)*a*_{3}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4,5,6}), 1, 4)*a*_{2}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4,5,6}), 1, 5)*a*_{1}: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4,5,6}), 1, 6)*b: = INDEX (LINEST (known_y's, known_x's ^ {1,2,3,4,5,6}), 1, 7)*

The array function **LINEST ()** calculates the linear trend that fits best the known data and then returns an array with statistics describing the trend line:

*= LINEST (known_y's, [known_x's], [const], [stats])*

where:

*known_y's*(required) are dependent data values,*known_x's*(optional) are independent data values:These variables cannot be omitted for the correct calculation for a polynomic trend. In any case, you should provide the

*known_x's*or the array*{1,2,3,...}*that is the same size as*known_y's*,*const*(optional) is a Boolean value specifying whether to force the parameter(intercept) to equal*b*:*0*- If
*const*is TRUE or omitted, the parameteris calculated normally,*b* - If
*const*is FALSE, the parameteris set equal to*b*.**0**

- If
*stats*(optional) is a Boolean value specifying whether to return additional regression statistics:- If
*stats*is TRUE, the**LINEST ()**function returns the additional regression statistics in the form of an array:where:

**Line****Statistics****Description**1 *a*_{n}*** *a*_{1}*b*Parameters of the function 2 **se**_{an}*** **se**_{a1}**se**_{b}Standard error of each parameter 3 **R**^{2}R-squared value **se**_{y}Standard error of estimate (also known as*y***Standard Error of a regression**) shows how big the prediction error is4 **F**F statistic (F-observed value) **d**_{r}Degrees of freedom 5 **ss**_{reg}Regression sum of squares (also known as **SSR**)**ss**_{resid}Residual sum of squares (also known as **SSE**) - If
*stats*is FALSE or omitted, the function returns only the calculated parameters.

- If

For the 6^{th} degree polynomial trend:

** Notes**:

- If you have a version of Microsoft Excel other than Microsoft 365, functions that return arrays must be entered as array functions using
**Ctrl+Shift+Enter**unless you can just press**Enter**. - Because the
**LINEST ()**function returns an array of data, you can use the**INDEX ()**function to display only the desired parameter(s) from the returned data array.

## Calculating the polynomial trendline values

To calculate the polynomial trendline values, Excel offers the array function **TREND ()** (see the notes above about array functions) that returns values along a linear trend. To work with this function for polynomial trends, just use the indicated powers of the *known_x's* instead of *known_x's*:

*= TREND (known_y's, [known_x's], [new_x's], [const])*

where:

*known_y's*(required) are dependent data values,*known_x's*(optional) are independent data values:These values cannot be omitted for the correct calculation for a polynomial trend. In any case, you should provide the

*known_x's*or the array*{1,2,3,...}*that is the same size as*known_y's*,*new_x's*(optional) are the new-values for which the**x****TREND ()**function should calculate corresponding-values:**y**- If you omit
*new_x's*, it is assumed to be the same as*known_x's*,

- If you omit
*const*(optional) is a Boolean value specifying whether to force the parameterto**b**:*0*- If
*const*is TRUE or omitted, the parameteris calculated normally,*b* - If
*const*is FALSE, the parameteris set to*b*.*0*

- If

## R-squared value

The **R-squared value** (in Excel, **R ^{2}**) calculates how well a trendline fits the data. The

**R-squared value**can vary from

**to**

*0***: the nearer**

*1***R**is to

^{2}**, the better the trendline fits the data.**

*1*The **R-squared value** can be displayed on the chart (see how to turn on the R-squared value). You can easily calculate that value using one of the methods:

- Use the
**LINEST ()**function to return the first statistics of the third line of the statistics array:*= INDEX (LINEST (known_y's, known_x's ^ {1,2,3,...}, , TRUE), 3, 1):* - Use the
**RSQ ()**function that returns the**R-squared value**of two data sets:*= RSQ (array1, array2)*where

*array1*and*array2*(both are required) are two data sets: