How to use LOGEST function in Excel?

The `LOGEST` function in Excel is used to perform an exponential regression analysis by fitting an exponential curve to your data and calculating the parameters of that curve. It’s commonly used for data that you suspect follows an exponential growth or decay pattern.

Here’s how you can use the `LOGEST` function in Excel:

Syntax

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

known_y’s: This is the required argument and refers to the dependent data values you want to analyze.
known_x’s: This is optional. These are the independent data values. If omitted, Excel assumes `x` values of 1, 2, 3, …, n.
const: Another optional argument. If set to `TRUE` or omitted, Excel will calculate the y-intercept (`b` in the equation y = b*m^x). If set to `FALSE`, the y-intercept is forced to be 1.
stats: Optional as well. If set to `TRUE`, `LOGEST` returns additional regression statistics as an array (e.g., standard errors). If `FALSE` or omitted, it only returns the slope coefficients.

Steps to Use LOGEST:

    =LOGEST(B2:B11, A2:A11, TRUE, TRUE)

Prepare Your Data: Ensure your data consists of at least two sets of numbers, one for the independent variable and one for the dependent variable.
Select Multiple Cells for Output: If you want to get more than just the main coefficients (e.g., statistical data), select a range of cells that matches the size of the output you expect. For only the coefficients, one row and as many columns as there are variables is enough. For full statistics, you’ll select a block that matches the shape of the data returned.
Enter the Formula: While having those cells selected, type the `LOGEST` formula into the formula bar. For example:

In this example:

Complete as an Array Formula (for older Excel versions): If you are using Excel versions prior to Excel 365 or Excel 2019, after typing in your formula, instead of pressing Enter, you’ll have to press `Ctrl + Shift + Enter` to enter it as an array formula.
Interpreting the Output:

The first output (or first row if more data is returned) will contain the coefficients for the curve: `m`, `b` from `y = b*m^x`.
If you’ve set the `stats` argument to `TRUE`, the additional statistics might include standard error for each coefficient, R-squared value, F Statistic, regression degrees of freedom, among others.

By using `LOGEST`, you can effectively model exponential relationships and use the resulting model to make predictions or better understand the trends in your dataset.

facebooklinkedin