LOGEST

What it does

LOGEST(known_ys, [known_xs], [const], [stats])

LOGEST(known_y's, [known_x's], [const], [stats]) fits the exponential model

y = b · m₁^x₁ · m₂^x₂ · … · m_k^x_k

and returns the bases m₁ … m_k together with the multiplier b.

The fit is not done in that form. Taking logarithms turns the model linear:

ln y = ln b + x₁·ln m₁ + … + x_k·ln m_k

so LOGEST runs an ordinary least-squares fit of ln y on the predictors — exactly the computation described on the LINEST page — and exponentiates the coefficients back:

m_j = exp( β̂_j ),    b = exp( β̂₀ )

This is the whole function, and it is also the whole caveat. The quantity minimised is

Σᵢ ( ln yᵢ − ln ŷᵢ )²

not Σ(yᵢ − ŷᵢ)². Least squares in log space weights relative error equally at every scale; least squares in the original space weights absolute error equally. On data spanning several orders of magnitude these two criteria give visibly different curves, and neither is "the" exponential fit — they answer different questions. If the noise in the data is multiplicative (a constant percentage), the log-space fit is the maximum-likelihood one and LOGEST is right. If the noise is additive, LOGEST systematically over-weights the small observations and a nonlinear least-squares fit is the correct tool. Excel has no worksheet function for that.

Domain. Every y value must be strictly positive — ln y is undefined otherwise — and the usual least-squares requirements apply to the design matrix: at least as many observations as parameters, full column rank. Range: the bases m_j and the multiplier b are strictly positive by construction, since they are exponentials. LOGEST can never return a negative or zero coefficient, which is a real modelling restriction: it cannot fit a decaying process to zero and cannot fit data that changes sign.

Interpretation of the bases: m_j is the multiplicative factor per unit increase in x_j. m = 1.05 means five percent growth per unit; m = 1 means no effect; m < 1 means decay.

Arguments

const = FALSE forces b = 1, not b = 0. In the log-linear model the constant is ln b, so suppressing the constant means ln b = 0, i.e. b = 1. The curve is then forced through (0, 1) rather than through the origin. This is the exponential analogue of LINEST's through-the-origin fit and it catches people who reason by analogy with LINEST without redoing the algebra.

The orientation rules — which axis carries observations and which carries predictors — are identical to LINEST's and are decided by the shapes of the two arguments, not by a flag.

Try it

Quirks

The returned array

Same shape and same reversal as LINEST:

{ m_k , m_{k−1} , … , m₁ , b }

with the multiplier last and the predictor bases in reverse column order. With stats = TRUE the five-row block is returned, laid out identically to LINEST's.

The statistics describe the log fit, not the exponential fit. The r², the standard errors, the F statistic and both sums of squares are computed on ln y. Consequences that matter in practice:

• se(m_j) in row 2 is the standard error of β̂_j = ln m_j, on the log scale. It is not the standard error of m_j. Converting requires the delta method: se(m_j) ≈ m_j · se(β̂_j) to first order, and that approximation degrades as se(β̂_j) grows.
• An r² from LOGEST and an r² from LINEST on the same raw data are not comparable. A higher r² from LOGEST does not mean the exponential model fits better in the original units; it means it fits better in log units, which is a different comparison.
• Because E[exp(Z)] > exp(E[Z]) for a non-degenerate Z (Jensen's inequality), the fitted curve ŷ = b·∏m_j^{x_j} is a fit to the conditional median of y, not its conditional mean, when the log-scale residuals are symmetric. Predictions from LOGEST are therefore systematically low as estimates of the mean. The classical correction multiplies by exp(σ̂²/2); whether to apply it is a modelling decision, and no Excel function applies it for you.

EV-MISC-0010 records the corresponding structural observation for this surface: with const FALSE the returned row still carries a trailing base cell set to one rather than being shortened, and multivariate coefficients come back in reverse predictor order. That record is explicit that these observations are structural and uncounted.

Result and edge cases

Returns an Array; a spilled dynamic array in modern Excel, historically a Ctrl+Shift+Enter formula.

• y values very close to 1 are the interesting numerical case; see Numerical notes.
• n = p — exact fit on the log scale, zero residual sum of squares, df = 0, and the statistics cells that need df > 0 become #N/A.
• Error values propagate.

Errors

How to implement

Numerical notes

Everything in LINEST's numerical notes applies to the inner fit — normal-equations conditioning, the κ₂(XᵀX) = κ₂(X)² squaring, centring and scaling, the NIST StRD record. Three hazards are specific to LOGEST.

1. Cancellation in ln y near 1. For y close to 1, ln y is a small difference computed from a value near unity, and the relative error in the logarithm blows up like 1/|ln y|. This is the classical reason log1p exists: ln(1 + t) computed as log(1 + t) loses digits once |t| ≲ 2⁻²⁶, because the addition 1 + t discards the low bits of t before the logarithm ever runs. Data that hovers near 1 — index series normalised to a base year, survival fractions near unity — is the case where LOGEST can lose most of its precision before the regression starts. A careful implementation cannot fix this from the outside: the y values arrive as doubles and the information is already gone. What it can do is compute the logarithm correctly rounded, so the loss is exactly the unavoidable one.

2. Exponentiation amplifies absolute error into relative error. The returned base is m = exp(β̂), so

δm / m  ≈  δβ̂

An absolute error of 10⁻¹⁰ in the fitted slope becomes a relative error of 10⁻¹⁰ in the base — acceptable — but the same error compounds when the base is raised to a large x in a prediction: m^x carries relative error x·δβ̂. Extrapolating an exponential fit fifty steps multiplies the coefficient error by fifty before you even consider the model risk. This is inherent to the parameterisation, not a defect.

3. The statistics block inherits the log-scale accumulation. SSE = Σ(ln yᵢ − ln ŷᵢ)² computed as SST − SSR cancels when the fit is good, and a good exponential fit is exactly when people look at r². Accumulating residuals directly is unconditionally better here, as in LINEST.

On the mathematical library side, the accuracy of LOGEST is bounded by the accuracy of the platform's log and exp. The correctly-rounded-or-nearly references are fdlibm's __ieee754_log and __ieee754_exp (Sun, and their descendants in glibc and musl), Cody and Waite's Software Manual for the Elementary Functions for the classic argument-reduction schemes, and Muller's Elementary Functions: Algorithms and Implementation for the modern treatment. A half-ulp log and a half-ulp exp are achievable; the Handbook does not assert what Excel uses.

Rust code

58 logest "LOGEST" (1, 4) [R "known_ys", R "known_xs", V "const", V "stats"] |s, a, sv| {
    regression(s, a, sv, k::regression_forecast_family::eval_logest_surface, RegressionNumbers::Coefficients)
}

fn logest_kernel(
    known_y: &NumericVector,
    known_x: &PreparedPredictorInput,
    use_const: bool,
    stats: bool,
) -> Result<CalcValue, WorksheetErrorCode> {
    let fit = exponential_fit_from_data(known_y, known_x, use_const)?;
    let model = &fit.model;
    let mut row = model
        .coefficients
        .iter()
        .rev()
        .map(|b| excel_numeric::excel_exp(*b))
        .collect::<Vec<_>>();
    row.push(if use_const {
        excel_numeric::excel_exp(model.intercept)
    } else {
        1.0
    });
    if stats {
        return Ok(statistics_block(&fit, use_const, row));
    }
    Ok(row_array(&row))
}

pub(crate) fn eval_logest_surface(
    args: &[CalcValue]
) -> Result<CalcValue, WorksheetErrorCode> {
    let known_y = collect_numeric_vector_arg(&args[0])?;
    let known_x_raw = args
        .get(1)
        .filter(|arg| !matches!(arg.core_ref(), CoreRef::Missing))
        .map(|arg| collect_numeric_matrix_arg(arg))
        .transpose()?;
    let known_x = predictor_input_from_optional(known_x_raw, &known_y)?;
    let use_const =
        bool_flag_from_eval_or_default(optional_arg_value(args.get(2))?, true)?;
    let stats = bool_flag_from_eval_or_default(optional_arg_value(args.get(3))?, false)?;
    logest_kernel(&known_y, &known_x, use_const, stats)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• LINEST — the linear engine LOGEST is built on. Mathematically, LOGEST(y, x, …) is EXP(LINEST(LN(y), x, …)) applied to the coefficient row. Whether the two agree in their last bits is a question requiring evidence; the composition route and the direct route can differ by where the logarithm is taken and how the statistics are accumulated.
• GROWTH — returns fitted y values from the same fit, as TREND does for LINEST. GROWTH is the function to use for prediction; LOGEST for the parameters.
• TREND — the linear-scale predictor, for the comparison a reader should make before choosing an exponential model.
• RRI and PDURATION — the financial two-point exponential-growth surfaces. RRI is exactly LOGEST on two observations with the constant fitted, expressed as a rate; it uses no least squares because two points determine the curve.
• Confused with: the exponential chart trendline (fitted on the plotted series, displayed with its own rounding), and with LOGEST's own r² as a measure of exponential fit quality — see above.

Named variants

No named variants are recorded yet.