GROWTH

What it does

GROWTH(known_ys, [known_xs], [new_xs], [const])

GROWTH(known_ys,[known_xs],[new_xs],[const]) fits an exponential relationship and returns a prediction for each new observation. With one predictor the model is y=b*m^x; with several, it is y=b*m1^x1*m2^x2*....

Omitting known x values substitutes positions one, two and so on. Omitting new x values predicts at the training positions. This second use returns fitted values, which need not equal noisy observed y values.

Try it

=GROWTH({3,9,27},{1,2,3},{4,5}) returns approximately {81,243}. The fitted mathematical model is 3^x, so each step triples the prediction.

To see the role of the constant, use =GROWTH({6,18,54},{1,2,3},{0,4}). The mathematical model is 2*3^x, giving approximately {2,162}. With the final const argument FALSE, the prediction at x=0 is one because the fitted multiplier b is fixed at one.

A two-predictor example is =GROWTH({2;6;10;30},{0,0;1,0;0,1;1,1},{2,1}), approximately 90. The observations fit 2*3^x1*5^x2; at (2,1), the prediction is 2*9*5.

Quirks

Known y values must be positive; zero or a negative observation gives #NUM!. The predictor layout matters when there is more than one independent variable:

With multiple predictors, known y must be a vector. New data needs the same number of predictors as training data. For one predictor, Microsoft also allows equally shaped known x and y ranges of other shapes.

const=FALSE fits a new model with b fixed at one: it sets the log-space intercept to zero and adjusts the slopes. It does not take a fit with an intercept and simply remove its constant afterward. Duplicate or nearly dependent predictor columns require a rank decision, which can affect coefficients and predictions.

How to implement

1. Prepare the observation vector, predictor columns and requested output layout. Apply omitted-x defaults before fitting.
2. Validate positive finite y values and obtain each stored public LN(y) result.
3. Fit a linear least-squares model to those logarithms. With a constant, the current code centres predictor columns and the transformed response on their forward-sum means. It uses a pivoted Householder QR fit, selecting the largest remaining predictor-column squared norm first; the constant column keeps its own position. Without a constant, it fits the raw transformed data with zero intercept.
4. For each new predictor vector, start P=1. In predictor order, compute the ordinary binary64 product u_j=coefficient_j*x_j, obtain the public EXP(u_j), and multiply it into P with ordinary binary64 multiplication.
5. Return EXP(intercept)*P, in the requested shape. Do not replace this prediction tree by EXP(intercept+sum(coefficient_j*x_j)): it stores different intermediates.

For the two-predictor example, the mathematical log coefficients are LN(2), LN(3) and LN(5). At (2,1), the separate exponentials represent factors two, nine and five, whose product is ninety. The calculated coefficients and exponentials can differ in their last bits from those mathematical values.

The source retains specific reflector stores and structural/numerical rank decisions, with remaining accumulator-layout questions. A generic QR library supplies a mathematical alternative, not the complete measured Excel fit. The outer prediction tree above is distinct from those unresolved fitting details, and no whole-function Handbook suite is published.

Rust code

56 growth "GROWTH" (1, 4) [R "known_ys", R "known_xs", R "new_xs", V "const"] |s, a, sv| {
    regression(s, a, sv, k::regression_forecast_family::eval_growth_surface, RegressionNumbers::Forecast)
}

/// A GROWTH prediction: the product of `EXP(b_j*x_j)` in predictor order, from 1, then
/// `EXP(intercept)` times it.
fn exponential_prediction(model: &LinearRegressionModel, x: &[f64]) -> f64 {
    let product = model
        .coefficients
        .iter()
        .zip(x.iter())
        .fold(1.0, |product, (b, x)| product * excel_numeric::excel_exp(b * x));
    excel_numeric::excel_exp(model.intercept) * product
}

/// The fit of LOGEST and GROWTH: the least-squares fit of the worksheet LN of each y (#NUM! for
/// a y that is not positive).
fn exponential_model_from_data(
    known_y: &NumericVector,
    known_x: &PreparedPredictorInput,
    use_const: bool,
) -> Result<LinearRegressionModel, WorksheetErrorCode> {
    exponential_fit_from_data(known_y, known_x, use_const).map(|fit| fit.model)
}

fn growth_kernel(
    known_y: &NumericVector,
    known_x: &PreparedPredictorInput,
    new_x: &PreparedPredictorInput,
    use_const: bool,
) -> Result<CalcValue, WorksheetErrorCode> {
    let model = exponential_model_from_data(known_y, known_x, use_const)?;
    if new_x.predictors.cols != model.coefficients.len() {
        return Err(WorksheetErrorCode::Ref);
    }
    let predicted = (0..new_x.predictors.rows)
        .map(|row_idx| exponential_prediction(&model, new_x.predictors.row_values(row_idx)))
        .collect::<Vec<_>>();
    eval_value_from_output_shape(&predicted, new_x.output_shape)
}

pub(crate) fn eval_growth_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 new_x_raw = args
        .get(2)
        .filter(|arg| !matches!(arg.core_ref(), CoreRef::Missing))
        .map(|arg| collect_numeric_matrix_arg(arg))
        .transpose()?;
    let new_x = prediction_input_from_optional(new_x_raw, &known_y, &known_x)?;
    let use_const =
        bool_flag_from_eval_or_default(optional_arg_value(args.get(3))?, true)?;
    growth_kernel(&known_y, &known_x, &new_x, use_const)
}

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

LOGEST returns exponential-model coefficients; TREND fits an additive linear model; FORECAST.LINEAR makes a one-predictor linear forecast.

Named variants

No named variants are recorded yet.