LSQ_LINEAR
This function solves a linear least-squares optimization problem with optional lower and upper bounds on the decision variables. It finds the vector that minimizes squared residual error while respecting variable limits.
The optimization problem is:
\min_x \; \frac{1}{2}\lVert Ax - b \rVert_2^2 \quad \text{subject to} \quad l \le x \le u
where A is the coefficient matrix, b is the observation vector, and l,u are optional bounds.
Excel Usage
=LSQ_LINEAR(a_matrix, b_vector, bounds_lower, bounds_upper, lsq_linear_method, tol, max_iter)a_matrix(list[list], required): Coefficient matrix A (m x n) with one row per equation.b_vector(list[list], required): Observation vector b (m x 1) with one entry per row of A.bounds_lower(list[list], optional, default: null): Lower bounds for each variable (1 x n row vector).bounds_upper(list[list], optional, default: null): Upper bounds for each variable (1 x n row vector).lsq_linear_method(str, optional, default: “trf”): Solver method to use for optimization.tol(float, optional, default: 1e-10): Tolerance for termination (must be positive).max_iter(int, optional, default: null): Maximum number of iterations for the solver.
Returns (list[list]): 2D list [[x1, x2, …, cost]], or error message string.
Example 1: Identity matrix with exact solution
Inputs:
| a_matrix | b_vector | |
|---|---|---|
| 1 | 0 | 1 |
| 0 | 1 | 2 |
Excel formula:
=LSQ_LINEAR({1,0;0,1}, {1;2})Expected output:
| Result | ||
|---|---|---|
| 1 | 2 | 0 |
Example 2: Bounded solution with constraints
Inputs:
| a_matrix | b_vector | bounds_lower | bounds_upper | |||
|---|---|---|---|---|---|---|
| 1 | 0 | 5 | 0 | -2 | 3 | 0 |
| 0 | 1 | -1 |
Excel formula:
=LSQ_LINEAR({1,0;0,1}, {5;-1}, {0,-2}, {3,0})Expected output:
| Result | ||
|---|---|---|
| 3 | -1 | 2 |
Example 3: Overdetermined system with BVLS solver
Inputs:
| a_matrix | b_vector | lsq_linear_method | |
|---|---|---|---|
| 1 | 1 | 1 | bvls |
| 1 | 2 | 2 | |
| 1 | 3 | 2 |
Excel formula:
=LSQ_LINEAR({1,1;1,2;1,3}, {1;2;2}, "bvls")Expected output:
| Result | ||
|---|---|---|
| 0.666667 | 0.5 | 0.0833333 |
Example 4: Custom tolerance and max iterations
Inputs:
| a_matrix | b_vector | bounds_lower | bounds_upper | tol | max_iter | |||
|---|---|---|---|---|---|---|---|---|
| 2 | -1 | 1 | -1 | -1 | 2 | 2 | 1e-8 | 200 |
| 1 | 1 | 3 | ||||||
| 1 | -1 | 0 |
Excel formula:
=LSQ_LINEAR({2,-1;1,1;1,-1}, {1;3;0}, {-1,-1}, {2,2}, 1e-8, 200)Expected output:
| Result | ||
|---|---|---|
| 1.35714 | 1.57143 | 0.0357143 |
Python Code
import numpy as np
from scipy.optimize import lsq_linear as scipy_lsq_linear
def lsq_linear(a_matrix, b_vector, bounds_lower=None, bounds_upper=None, lsq_linear_method='trf', tol=1e-10, max_iter=None):
"""
Solve a bounded linear least-squares problem.
See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.lsq_linear.html
This example function is provided as-is without any representation of accuracy.
Args:
a_matrix (list[list]): Coefficient matrix A (m x n) with one row per equation.
b_vector (list[list]): Observation vector b (m x 1) with one entry per row of A.
bounds_lower (list[list], optional): Lower bounds for each variable (1 x n row vector). Default is None.
bounds_upper (list[list], optional): Upper bounds for each variable (1 x n row vector). Default is None.
lsq_linear_method (str, optional): Solver method to use for optimization. Valid options: Trust Region Reflective, Bounded Variable Least Squares. Default is 'trf'.
tol (float, optional): Tolerance for termination (must be positive). Default is 1e-10.
max_iter (int, optional): Maximum number of iterations for the solver. Default is None.
Returns:
list[list]: 2D list [[x1, x2, ..., cost]], or error message string.
"""
try:
def to2d(x):
return [[x]] if not isinstance(x, list) else x
# Normalize 2D list inputs
a_matrix = to2d(a_matrix)
b_vector = to2d(b_vector)
# Validate coefficient matrix
try:
a_mat = np.array(a_matrix, dtype=float)
except Exception:
return "Error: Invalid input: a_matrix must be a 2D list of numeric values."
if a_mat.ndim != 2:
return "Error: Invalid input: a_matrix must be two-dimensional."
# Validate observation vector
try:
b_vec = np.array(b_vector, dtype=float).flatten()
except Exception:
return "Error: Invalid input: b_vector must be a 2D list of numeric values."
if b_vec.size != a_mat.shape[0]:
return "Error: Invalid input: Length of b_vector must equal number of rows in a_matrix."
n_vars = a_mat.shape[1]
# Process bounds
lower = None
upper = None
if bounds_lower is not None:
bounds_lower = to2d(bounds_lower)
try:
lower_array = np.array(bounds_lower, dtype=float)
lower = lower_array.flatten() if lower_array.ndim > 1 else lower_array
except Exception:
return "Error: Invalid input: bounds_lower must contain numeric values."
if lower.size != n_vars:
return "Error: Invalid input: bounds_lower must have one value per variable."
if bounds_upper is not None:
bounds_upper = to2d(bounds_upper)
try:
upper_array = np.array(bounds_upper, dtype=float)
upper = upper_array.flatten() if upper_array.ndim > 1 else upper_array
except Exception:
return "Error: Invalid input: bounds_upper must contain numeric values."
if upper.size != n_vars:
return "Error: Invalid input: bounds_upper must have one value per variable."
if lower is not None and upper is not None:
if np.any(lower > upper):
return "Error: Invalid input: Each lower bound must be less than or equal to the corresponding upper bound."
if lower is None:
lower = np.full(n_vars, -np.inf)
if upper is None:
upper = np.full(n_vars, np.inf)
bounds = (lower, upper)
# Validate method
valid_methods = {'trf', 'bvls'}
if lsq_linear_method not in valid_methods:
return f"Error: Invalid method: {lsq_linear_method}. Must be one of: {', '.join(sorted(valid_methods))}"
# Validate tolerance and max_iter
try:
tol = float(tol)
except (TypeError, ValueError):
return "Error: Invalid input: tol must be a float."
if tol <= 0:
return "Error: Invalid input: tol must be positive."
max_iter_param = None
if max_iter is not None:
try:
max_iter_param = int(max_iter)
except (TypeError, ValueError):
return "Error: Invalid input: max_iter must be an integer."
if max_iter_param <= 0:
return "Error: Invalid input: max_iter must be positive."
try:
result = scipy_lsq_linear(
a_mat,
b_vec,
bounds=bounds,
method=lsq_linear_method,
tol=tol,
max_iter=max_iter_param,
)
except ValueError as exc:
return f"Error: lsq_linear failed: {exc}"
except Exception as exc:
return f"Error: lsq_linear failed: {exc}"
if not result.success:
return f"Error: lsq_linear failed: {result.message}"
try:
solution_vector = [float(val) for val in result.x]
except (TypeError, ValueError):
return "Error: Failed to convert solution vector to floats."
cost = float(result.cost) if result.cost is not None else float(np.sum((a_mat @ result.x - b_vec) ** 2) / 2.0)
return [solution_vector + [cost]]
except Exception as e:
return f"Error: {str(e)}"Online Calculator
Coefficient matrix A (m x n) with one row per equation.
Observation vector b (m x 1) with one entry per row of A.
Lower bounds for each variable (1 x n row vector).
Upper bounds for each variable (1 x n row vector).
Solver method to use for optimization.
Tolerance for termination (must be positive).
Maximum number of iterations for the solver.