LOGEST function

The LOGEST function in Excel performs exponential regression to calculate the parameters of the exponential curve that best fits a set of data points. The function returns an array of values that describe the relationship between the independent variable (x) and the dependent variable (y), assuming that the relationship follows an exponential model.

The exponential regression model is typically represented as:

$$y = b \cdot e^{mx}$$

Where:

• $y$ is the dependent variable.
• $x$ is the independent variable.
• $b$ is a constant that represents the intercept (the value of $y$ when $x = 0$).
• $m$ is the slope (the rate of change) of the exponential curve.
• $e$ is Euler’s number (approximately 2.71828).

Syntax:

LOGEST(known_y's, known_x's, [const], [stats])

Arguments:

• known_y’s: Required. The range or array of dependent values (y-values), which represent the observed outcomes or the output that you’re trying to model.
• known_x’s: Required. The range or array of independent values (x-values), which represent the input or predictor variable.
• const: Optional. A logical value (TRUE or FALSE) that specifies whether to force the constant $b$ to be 1.
  • TRUE or omitted: The function will calculate the constant $b$.
  • FALSE: The function will set the constant $b$ to 1, forcing the curve to pass through the origin (0,0).
• stats: Optional. A logical value (TRUE or FALSE) that specifies whether to return additional regression statistics.
  • TRUE: Returns additional statistics about the regression.
  • FALSE (or omitted): Only returns the parameters $b$ and $m$.

Output:

• If stats is set to FALSE (or omitted), the function returns an array containing:
  • Exponent $m$: The rate of growth (slope) of the exponential curve.
  • Constant $b$: The constant or intercept of the exponential curve.
• If stats is set to TRUE, the function returns an array containing:
  • Exponent $m$
  • Constant $b$
  • Standard error of the exponent
  • Standard error of the constant
  • R-squared value: A measure of how well the data fit the exponential model.
  • F-statistic: A measure of how well the model explains the data, used for testing the hypothesis about the model.
  • Degrees of freedom
  • Regression sum of squares
  • Residual sum of squares

How It Works:

The LOGEST function uses the method of least squares to fit an exponential curve to the data, and it calculates the values of $m$ and $b$ that minimize the sum of the squared differences between the observed and predicted $y$-values. It is essentially solving for the parameters of the equation $y = b \cdot e^{mx}$ that best fit the data.

Example:

1. Example 1: Basic Exponential Regression Suppose you have the following data:
   • x-values: 1, 2, 3, 4, 5 (in cells A1:A5)
   • y-values: 2, 4, 8, 16, 32 (in cells B1:B5)

   To perform exponential regression and find the parameters $b$ and $m$, use the formula:

   =LOGEST(B1:B5, A1:A5)
   

   This will return two values:

   • b: The constant of the exponential model.
   • m: The exponent of the exponential model.

   The output will be something like:

   • b = 1 (constant)
   • m = 1 (rate of growth)

   This corresponds to the equation $y = 1 \cdot e^{x}$.

2. Example 2: Including Additional Statistics If you want to return additional statistics, such as the standard errors and R-squared value, use:

   =LOGEST(B1:B5, A1:A5, TRUE, TRUE)
   

   This will return multiple values, including:

   • Exponent $m$
   • Constant $b$
   • Standard errors
   • R-squared
   • F-statistic
   • Other regression statistics

Key Points:

• The LOGEST function is used when the relationship between the independent and dependent variables is assumed to be exponential (i.e., the dependent variable grows at a constant percentage rate).
• The exponent $m$ represents the rate of growth or decay.
• The constant $b$ represents the value of $y$ when $x = 0$.
• The function is based on the least-squares regression method to fit the exponential model to the data.
• The LOGEST function can handle multiple independent variables (i.e., multiple exponential regression), though it is most commonly used for simple exponential regression.

Use Cases:

• Growth Modeling: The LOGEST function is frequently used in fields like biology, economics, or finance, where growth or decay is exponential (e.g., population growth, compound interest, radioactive decay).
• Forecasting: It can be used to forecast future values based on past trends when the data shows exponential growth or decay.
• Scientific Research: In scientific studies, LOGEST can help model phenomena like viral growth, the spread of diseases, or the rate of chemical reactions, which often follow exponential patterns.

Example Interpretation:

• Exponent $m$: Describes how rapidly the dependent variable changes with the independent variable. A positive $m$ indicates exponential growth, while a negative $m$ indicates exponential decay.
• Constant $b$: Indicates the starting value or initial amount at $x = 0$. If $b$ is 1, it means that the curve passes through the origin.

Notes:

• The LOGEST function assumes the relationship between the data follows an exponential model. If the data does not fit this model well, the results may not be meaningful.
• The LOGEST function is an array function, so when you use it to return more than one value (e.g., for the slope and intercept), you need to press Ctrl+Shift+Enter.
• If you force the constant $b$ to 1 by setting const to FALSE, the regression line will pass through the origin, and the model will represent pure exponential growth or decay starting from zero.