# Exponential trend equation and forecast

**trend line**is exponential. See more about the different types of trendlines you can create in Excel:

Excel uses the **least squares** method to find the best fitting line for the data points.

When calculating 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*).

## Exponential trendline equation and formulas

The exponential trendline is a curved line described by the function:

*y = a * e ^{bx}*

where:

and*a*are the parameters of the approximating function (also named function*b**coefficients*or*constants*),is the mathematical constant, the base of the natural logarithm (also known as*e**Euler's number*, approximately equal to*2.71828...*- the result of**EXP (1)**- see below the description of this function).

** Note**: Excel uses a linear model to fit an exponential function: the exponential function

**is transformed into a linear function using a change of variables. After changing the variables, the calculations are similar to the linear case**

*y = a * e*^{bx}**Y =**:

*b * x*+ A- For
, the variables change to*a*> 0**Y = ln (**and*y*)**A = ln (**,*a*) - For
(there is some upper bound*a*< 0for*c*), the variables change to*y***Y = ln (**and-*c*)*y***A = ln (**.*-a*)To find parameter

, it is necessary to perform the inverse transformation*a*or*a*= EXP (A)**EXP (-A)**.

Excel offers the following functions to calculate the trend parameters:

**Using LINEST function:**

*a: = EXP (INDEX (LINEST (LN (known_y's), known_x's), 1, 2))**b: = INDEX (LINEST (LN (known_y's), known_x's), 1)*

**Using LOGEST function:**

*a: = INDEX (LOGEST (known_y's, known_x's), 1, 2))**b: = LN (INDEX (LOGEST (known_y's, known_x's), 1)*

** Note**: You can use any of these functions to get different parameters. For example:

*a: = INDEX (LOGEST (known_y's, known_x's), 1, 2))**b: = INDEX (LINEST (LN (known_y's), known_x's), 1)*

The **EXP ()** function returns the result of the constant ** e** raised to the power of a number:

*= EXP (number)*

where:

*number*(required) is a power thatis raised to.*e*

The **LN ()** function is the inverse of the **EXP ()** function and returns the natural logarithm of a given *number*:

*= LN (number)*

where:

*number*(required) is a number to take the natural logarithm of.

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

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

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

*= LOGEST (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. If*known_x's*is omitted, it is assumed to be the array*{1,2,3,...}*that has the same size as*known_y's*,*const*(optional) is a Boolean value specifying whether to force the parameterto equal*a*(or*1***A**set to). Those, the exponential curve would pass through the point (*0**x = 0, y = 1*):- If
*const*is TRUE or omitted, the parameteris calculated normally,*a* - If
*const*is FALSE, the parameteris set to*a*, so the function will look like**1**.*y = e*^{bx}

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

**Line****Statistics****Description**1 *b**a*Parameters of the function 2 **se**_{b}**se**_{a}Standard error of parameters 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

** 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 ()**and**LOGEST ()**functions return an array of data, you can use the**INDEX ()**function to display only the desired parameter(s) from the returned data array. - Because the statistics are calculated for the linear model, the statistics array and chart values are different.

## Calculating the exponential trendline values

To calculate the exponential trendline values, Excel offers the array function **GROWTH ()** (see the notes above about array functions) that returns values for the exponential trend:

*= GROWTH (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. If*known_x's*is omitted, it is assumed to be the array*{1,2,3,...}*that has the same size as*known_y's*,*new_x's*(optional) are the new-values for which the**x****GROWTH ()**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 both
*known_x's*and*new_x's*, they are assumed to be the array*{1,2,3,...}*that is the same size as*known_y's*.

- If you omit
*const*(optional) is a Boolean value specifying whether to force the parameter(intercept) to equal**a**. Those, the curved line would pass through the point (*1**x = 0*,*y = 1*):- If
*const*is TRUE or omitted, the parameteris calculated normally,*a* - If
*const*is FALSE, the parameteris set to*a*, so the formula will look like*1*.*y = e*^{bx}

- If

** Note**: You can get the same results using the function

**TREND ()**as for linear trend:

*= EXP (TREND (LN (known_y's)))*

## R-squared value

The **R-squared value** calculates for exponential regression in the **LINEST ()** and **LOGEST ()** functions statistics incorrectly because of using the linear model! You can see different values in the statistics array and on the chart.

The **R-squared value** (in Excel, **R ^{2}**) determines how well the 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** displayed on the chart (see how to turn on the R-squared value) shows the Pearson correlation. You can easily calculate that value:

- 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:

** Notes**: The value you see in the

**LINEST ()**and

**LOGEST ()**statistics is the

**R-squared value**between the logarithms of

*array1*and

*array2*. In this example:

## Forecast using the exponential trendline

Using the **GROWTH ()** function, you can calculate the predicted values ** y** for the

*new_x's*:

** Note**: You can get the same results using the function

**TREND ()**with the following transformation:

*= EXP (TREND (LN (known_y's), , new_x's))*