LSTSQ

Overview

The LSTSQ function computes the least-squares solution to the linear matrix equation $Ax = b$ , minimizing the 2-norm $\|b - Ax\|$ . This is commonly used to solve overdetermined or underdetermined systems, perform linear regression, or fit models to data. The function wraps scipy.linalg.lstsq, which uses LAPACK drivers to efficiently solve the least-squares problem and returns the solution vector $x$ , the sum of squared residuals, the effective rank of $A$ , and the singular values of $A$ .

The least-squares solution solves:

\min_x \|b - Ax\|_2

For more details, see the scipy.linalg.lstsq documentation .

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

Usage

To use the function in Excel:


=LSTSQ(A, B, [cond], [lapack_driver], [return_type])

A (2D list, required): Coefficient matrix (M rows × N columns).
B (2D list, required): Right-hand side matrix (M rows × K columns or a single column for vector).
cond (float, optional, default=None): Cutoff for small singular values. Singular values smaller than cond * largest_singular_value are considered zero.
lapack_driver (str, optional, default=“gelsd”): LAPACK driver to use. Options: "gelsd", "gelsy", "gelss".
return_type (str, optional, default=“solution”): Which value to return: "solution", "residuals", "rank", or "singular_values".

The function returns the selected value as specified by return_type, or an error message as a 2D list if the input is invalid.

Examples

Example 1: Solution Vector

Solve $Ax = B$ for $x$ where:

A
1	1	0
1	0	1
1	1	1
1	0	0

B
2
2
3
1

Excel formula:


=LSTSQ({1,1,0;1,0,1;1,1,1;1,0,0}, {2;2;3;1}, 1e-10, "gelsd", "solution")

Expected output:

x
1.0
1.0
1.0

Example 2: Residuals


=LSTSQ({1,1,0;1,0,1;1,1,1;1,0,0}, {2;2;3;1}, 1e-10, "gelsd", "residuals")

Expected output:

| 0.0 |

Example 3: Rank


=LSTSQ({1,1,0;1,0,1;1,1,1;1,0,0}, {2;2;3;1}, 1e-10, "gelsd", "rank")

Expected output:

| 3 |

Example 4: Singular Values


=LSTSQ({1,1,0;1,0,1;1,1,1;1,0,0}, {2;2;3;1}, 1e-10, "gelsd", "singular_values")

Expected output:

| 2.52 | 1.00 | 0.79 |

This means, for example, the least-squares solution to the first system is $x_1 = 1.0, x_2 = 1.0, x_3 = 1.0$ .

Python Code


from scipy.linalg import lstsq as scipy_lstsq
 
def lstsq(A, B, cond=None, lapack_driver="gelsd", return_type="solution"):
    """
    Compute the least-squares solution to Ax = B using scipy.linalg.lstsq.
 
    Args:
        A: 2D list, coefficient matrix (M x N).
        B: 2D list, right-hand side (M x K or M x 1).
        cond: float, optional, cutoff for small singular values.
        lapack_driver: str, optional, LAPACK driver to use ("gelsd", "gelsy", "gelss").
        return_type: str, which value to return: "solution", "residuals", "rank", or "singular_values".
 
    Returns:
        The selected value as specified by return_type, or error message as 2D list.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Validate input
    import numpy as np
    try:
        A_arr = np.array(A, dtype=float)
        B_arr = np.array(B, dtype=float)
    except Exception:
        return [["Invalid input: A and B must be 2D lists of numbers."]]
    if A_arr.ndim != 2 or B_arr.ndim not in [1,2]:
        return [["Invalid input: A must be 2D and B must be 1D or 2D."]]
    if A_arr.shape[0] != B_arr.shape[0]:
        return [["Invalid input: Number of rows in A and B must match."]]
    if cond is not None:
        try:
            cond = float(cond)
        except Exception:
            return [["Invalid input: cond must be a float or None."]]
    if lapack_driver not in ["gelsd", "gelsy", "gelss"]:
        return [["Invalid input: lapack_driver must be 'gelsd', 'gelsy', or 'gelss'."]]
    if return_type not in ["solution", "residuals", "rank", "singular_values"]:
        return [["Invalid input: return_type must be one of 'solution', 'residuals', 'rank', 'singular_values'."]]
    try:
        x, res, rank, s = scipy_lstsq(A_arr, B_arr, cond=cond, lapack_driver=lapack_driver)
    except Exception as e:
        return [[f"scipy.linalg.lstsq error: {e}"]]
    # Always return 2D lists
    def to_2d(val):
        arr = np.atleast_2d(val)
        return arr.tolist()
    if return_type == "solution":
        return to_2d(x)
    elif return_type == "residuals":
        return to_2d(res)
    elif return_type == "rank":
        return [[rank]]
    elif return_type == "singular_values":
        return [s.tolist()]

Live Notebook

Edit this function in a live notebook .

LSTSQ

Overview

Usage

Examples

Python Code

Live Notebook

Live Demo