LEAST_SQUARES

Overview

The LEAST_SQUARES function solves nonlinear least-squares problems using the SciPy optimization library. It fits a user-defined model function to observed data by minimizing the sum of squared residuals. This is useful for curve fitting, parameter estimation, and regression analysis directly in Excel.

The least-squares method seeks to find the parameter vector $\mathbf{p}$ that minimizes the sum of squared residuals:

S(\mathbf{p}) = \sum_{i=1}^n (y_i - f(x_i, \mathbf{p}))^2

where $y_i$ are observed values, $x_i$ are input values, and $f(x, \mathbf{p})$ is the model function. SciPy’s least_squares supports bounds and several algorithms (“trf”, “dogbox”, “lm”).

This example function is provided as-is without any representation of accuracy.

Usage

To use the LEAST_SQUARES function in Excel, enter it as a formula in a cell, specifying the model, data, and initial parameters:


=LEAST_SQUARES(model, xdata, ydata, p_zero, [bounds_lower], [bounds_upper], [method])

model (string, required): Model function as a string, e.g., “a * x + b” or “a * exp(b * x)”. Use variable x and parameter names (e.g., a, b).
xdata (2D list of float, required): 2D list or column of x values. Example: {1;2;3}
ydata (2D list of float, required): 2D list or column of y values. Example: {2;4;6}
p_zero (2D list of float, required): 2D list or row of initial parameter guesses. Example: {1,1}
bounds_lower (2D list of float, optional): Lower bounds for each parameter. Example: {0,0}
bounds_upper (2D list of float, optional): Upper bounds for each parameter. Example: {10,10}
method (string, optional): Optimization method (“trf”, “dogbox”, “lm”). Example: “trf”

The function returns the fitted parameter values as a single row (2D list of float). If the fit fails, an error message string is returned.

Examples

Example 1: Linear Fit


=LEAST_SQUARES("a * x + b", {1;2;3}, {2;4;6}, {1,1})

Expected output:

a	b
2.0	0.0

Example 2: Exponential Fit


=LEAST_SQUARES("a * exp(b * x)", {1;2;3}, {2.7;7.4;20.1}, {1,1})

Expected output:

a	b
1.0	1.0

Example 3: Linear Fit with Bounds


=LEAST_SQUARES("a * x + b", {1;2;3}, {2;4;6}, {1,1}, {0,0}, {10,10})

Expected output:

a	b
2.0	0.0

Python Code


import numpy as np
from scipy.optimize import least_squares as scipy_least_squares
import math
SAFE_GLOBALS = {k: getattr(math, k) for k in dir(math) if not k.startswith("_")}
SAFE_GLOBALS["np"] = np
SAFE_GLOBALS["numpy"] = np
SAFE_GLOBALS["exp"] = np.exp
SAFE_GLOBALS["log"] = np.log
SAFE_GLOBALS["sin"] = np.sin
SAFE_GLOBALS["cos"] = np.cos
SAFE_GLOBALS["tan"] = np.tan
SAFE_GLOBALS["abs"] = abs
SAFE_GLOBALS["pow"] = pow
 
def least_squares(model, xdata, ydata, p_zero, bounds_lower=None, bounds_upper=None, method=None):
    """
    Solve a nonlinear least-squares problem by fitting a user-defined model to data.
 
    Args:
        model (str): Model function as a string, e.g., "a * x + b". Use variable `x` and parameter names.
        xdata (list[list[float]]): 2D list of input x values (independent variable).
        ydata (list[list[float]]): 2D list of observed y values (dependent variable).
        p_zero (list[list[float]]): 2D list of initial guesses for parameters.
        bounds_lower (list[list[float]], optional): 2D list of lower bounds for parameters.
        bounds_upper (list[list[float]], optional): 2D list of upper bounds for parameters.
        method (str, optional): Optimization method ("trf", "dogbox", "lm").
 
    Returns:
        list[list[float]]: Fitted parameter values as a single row, or
        str: Error message if calculation fails.
 
    This example function is provided as-is without any representation of accuracy.
    """
    try:
        x = np.array(xdata).flatten()
        y = np.array(ydata).flatten()
        p_zero = np.array(p_zero).flatten()
        n_params = len(p_zero)
        import re
        param_names = re.findall(r'\b[a-zA-Z_]\w*\b', model)
        param_names = [name for name in param_names if name not in ("x", "exp", "log", "sin", "cos", "tan", "abs", "pow")]
        param_names = list(dict.fromkeys(param_names))
        if len(param_names) != n_params:
            return f"Number of initial guesses (p_zero) does not match number of parameters in model: {param_names}"
        if bounds_lower is not None and bounds_upper is not None:
            bounds = (np.array(bounds_lower).flatten(), np.array(bounds_upper).flatten())
        else:
            bounds = (-np.inf, np.inf)
        def residuals(params):
            local_dict = dict(zip(param_names, params))
            local_dict["x"] = x
            try:
                y_pred = eval(model, SAFE_GLOBALS, local_dict)
            except Exception:
                return np.full_like(y, np.nan)
            return y_pred - y
        lsq_method = method if method is not None else 'trf'
        if lsq_method not in ('trf', 'dogbox', 'lm'):
            return "`method` must be 'trf', 'dogbox' or 'lm'."
        result = scipy_least_squares(residuals, p_zero, bounds=bounds, method=lsq_method)
        if not result.success:
            return f"Fit failed: {result.message}"
        return [result.x.tolist()]
    except Exception as e:
        return str(e)

Live Notebook

Edit this function in a live notebook .

LEAST_SQUARES

Overview

Usage

Examples

Python Code

Live Notebook

Live Demo