LOGEST Function (LibreOffice Calc)
The LOGEST function in LibreOffice Calc performs exponential regression and returns statistics describing the best-fit exponential curve. This guide explains syntax, array behavior, regression output, examples, errors, and best practices.
y = b * m^x
It returns the exponential growth rate, base, intercept, and full regression statistics. LOGEST is essential for modeling growth, decay, forecasting, and any process that follows an exponential pattern.
This guide provides syntax, array behavior, output structure, examples, advanced modeling techniques, common errors, and best practices, making it one of the most complete LOGEST references available for LibreOffice Calc.
What the LOGEST Function Does
- Fits data to an exponential curve
- Returns growth factor(s), intercept, and full regression statistics
- Supports multi-variable exponential regression
- Works as an array function
- Ideal for forecasting, growth modeling, and scientific analysis
- Works across sheets
LOGEST is the exponential counterpart to LINEST.
Syntax
LOGEST(known_y; known_x; const; stats)
Where:
known_y— dependent variable (must be positive)known_x— independent variable(s)const— TRUE = calculate intercept; FALSE = force intercept = 1stats— TRUE = return full regression statistics; FALSE = return only coefficients
Older Calc versions require Ctrl+Shift+Enter.
Modern Calc spills automatically.
Output Structure
When stats = FALSE:
| Column 1 | Column 2 |
|---|---|
| m (base) | b (intercept) |
The model is:
y = b * m^x
When stats = TRUE, LOGEST returns a 5-row array, identical in structure to LINEST:
| Row | Meaning |
|---|---|
| 1 | m (base), b (intercept) |
| 2 | Standard errors |
| 3 | R², Standard error of Y estimate |
| 4 | F-statistic, Degrees of freedom |
| 5 | Regression sum of squares, Residual sum of squares |
Basic Examples
Simple exponential regression (Y vs X)
=LOGEST(B1:B10; A1:A10)
Regression with intercept forced to 1
=LOGEST(B1:B10; A1:A10; FALSE)
Full regression statistics
=LOGEST(B1:B10; A1:A10; TRUE; TRUE)
Regression across sheets
=LOGEST(Sheet1.B1:B50; Sheet2.A1:A50)
Advanced Examples
Predict Y using LOGEST coefficients
Base (m):
=INDEX(LOGEST(B1:B10; A1:A10); 1)
Intercept (b):
=INDEX(LOGEST(B1:B10; A1:A10); 1; 2)
Prediction:
=INDEX(LOGEST(B1:B10; A1:A10); 1; 2) * INDEX(LOGEST(B1:B10; A1:A10); 1) ^ X
Use LOGEST inside GROWTH for forecasting
=GROWTH(B1:B10; A1:A10; A11:A20)
Exponential regression ignoring errors
=LOGEST(IF(ISNUMBER(B1:B10); B1:B10); A1:A10)
(Confirm with Ctrl+Shift+Enter in older Calc.)
Exponential regression on visible cells only
Use SUBTOTAL helper column to filter X/Y before passing to LOGEST.
Multi-variable exponential regression
=LOGEST(Y1:Y20; X1:Z20; TRUE; TRUE)
Log-linear transformation (manual equivalent)
=LINEST(LN(B1:B10); A1:A10)
LOGEST automates this transformation.
Requirements and Constraints
- All Y values must be positive (because LOGEST uses natural logs internally)
- X values may be any real numbers
- Multi-variable regression requires matching row counts
Common Errors and Fixes
Err:502 — Invalid argument
Occurs when:
- Y values contain zero or negative numbers
- X and Y ranges have mismatched lengths
- Non-numeric text included
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Array dimensions are invalid
LOGEST returns #N/A
Occurs when:
- Not enough data points
- Forced intercept creates invalid model
- Predictors are linearly dependent
R² seems too low or too high
Possible causes:
- Data is not exponential
- Outliers distort the curve
- X values not scaled
Best Practices
- Use LOGEST for growth/decay modeling and exponential forecasting
- Use GROWTH for direct exponential predictions
- Always inspect R² and residuals
- Remove or investigate outliers
- Ensure Y values are strictly positive
- Use named ranges for cleaner formulas