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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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
LOGEST returns an array.
Older Calc versions require Ctrl+Shift+Enter.
Modern Calc spills automatically.
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
LOGEST is the key to modeling exponential processes — population growth, decay curves, compounding, and more. Master it, and you unlock a powerful class of predictive analytics.