MINVERSE

What it does

MINVERSE(array)

MINVERSE(array) returns the matrix inverse of a square matrix: the unique matrix A⁻¹ with

A · A⁻¹ = A⁻¹ · A = I

Existence. A⁻¹ exists exactly when A is nonsingular, equivalently when det A ≠ 0, equivalently when the columns are linearly independent, equivalently when the only solution of Ax = 0 is x = 0. Microsoft's page states the criterion in the determinant form: "Some square matrices cannot be inverted and will return the #NUM! error value with MINVERSE. The determinant for a noninvertable matrix is 0."

The closed form — and the reason it must not be used as an algorithm — is Cramer's rule:

A⁻¹ = adj(A) / det(A)

where adj(A) is the transpose of the cofactor matrix. For a 2×2 matrix this is the familiar

[a b; c d]⁻¹ = 1/(ad − bc) · [d −b; −c a]

Properties worth carrying: (AB)⁻¹ = B⁻¹A⁻¹ (note the order reversal); (Aᵀ)⁻¹ = (A⁻¹)ᵀ; det(A⁻¹) = 1/det(A); and the inverse of a diagonal matrix is the elementwise reciprocal. The map A ↦ A⁻¹ is smooth on the open set of nonsingular matrices, with derivative −A⁻¹ (dA) A⁻¹ — which is the analytic statement of the fact that inversion is badly behaved near singularity: the derivative blows up as A⁻¹ does.

Domain and range. Domain: nonsingular square numeric matrices. Range: nonsingular square matrices. There is no pole in the analytic sense, but the set of singular matrices is a measure-zero hypersurface (the zero set of the determinant polynomial) across which the function is unbounded — the closest thing to a singularity a matrix function has, and the origin of every difficulty below.

Arguments

Exactly one argument; the declared arity is one to one. Microsoft's page notes the array may be given as a cell range such as A1:C3, an array constant such as {1,2,3;4,5,6;7,8,9}, or a name.

Result shape. MINVERSE returns an array the same size as its input. Microsoft's page states the entry mechanics explicitly: in current Microsoft 365 the formula is entered in the top-left cell and confirmed with Enter, spilling; in earlier versions the output range must be selected first and the formula confirmed with Ctrl+Shift+Enter as a legacy array formula. That difference is a property of the host, not of the function.

Try it

Quirks

Result and edge cases

Returns Array of Number.

• A 1×1 zero matrix produces #NUM! — singular, correctly.
• A 1×1 matrix holding −1 returns a 1×1 array holding −1, its own reciprocal.
• A logical argument and numeric text both produce #VALUE!. As with MDETERM, the matrix family refuses conversions that the scalar math functions accept. The text refusal is documented; the logical refusal is not.
• The largest finite double as a 1×1 matrix returns its reciprocal, a subnormal-range value.
• The smallest positive subnormal as a 1×1 matrix produces #NUM! — not a reciprocal. Mathematically the matrix is nonsingular; its inverse simply is not representable, since the reciprocal overflows. The engine reports it as the singular case. The documented criterion is "the determinant is 0", and this matrix's determinant is not zero, so the observable behaviour and the documented rule part company here. Whatever the right design is, the honest statement is that #NUM! from MINVERSE covers at least two distinct conditions — genuinely singular, and representably impossible — and only one of them is documented.
• An inline 2×2 integer array returns an inverse whose entries are not the exactly representable values the true inverse has. The exact inverse of that particular matrix has entries every one of which is a binary64 value; the returned entries differ from them in the last bits. This is not a defect report against anyone — it is the expected signature of a factorisation-based solve, and it is exactly why this function has a live numeric investigation attached. See "Numerical notes".

Errors

Error values inside the array propagate; the family declares a reduction error-collapse profile, so competing errors are folded rather than surfacing independently (coercion and lifting).

On the documented #NUM! criterion. "The determinant is 0" is the mathematical criterion and is not a computational test — see the singularity discussion on MDETERM. No implementation can determine exact singularity from rounded inputs; what it can do is detect a pivot that has become negligible relative to the matrix's scale. So the documented rule describes the intent, and the observed threshold behaviour is a different, and undocumented, thing. The subnormal case above shows the gap is real.

On the accuracy remark. Microsoft's page states:

"MINVERSE is calculated with an accuracy of approximately 16 digits, which may lead to a small numeric error when the cancellation is not complete."

As on MDETERM, this is not an error bound: "approximately 16 digits" describes binary64 generally, and "when the cancellation is not complete" names no determinable condition. The real bound is conditioning-dependent and is stated below.

How to implement

Numerical notes

The algorithm space, and what has been ruled out of it

• Adjugate / cofactor (Cramer's rule) — ruled out at both sizes tested. Cramer's rule is the textbook formula and is numerically the worst of the candidates; it is also O(n!) if implemented literally.
• An x87 80-bit extended-precision body with double stores — ruled out, and strictly worse than plain binary64. Note the contrast with LN and LOG, where the x87 substrate is exactly what was identified. Different Excel surfaces sit on different substrates, and that is a finding in itself.
• A systematic sweep of solve orderings — forward against back substitution, streamed against sum-then-subtract accumulation, ascending against descending index order, division against reciprocal-multiply — all eliminated. This is the shape of the last-bit problem: the op graph, not the algorithm name, is what determines the final bits.

Why the last bits are hard at all

Conditioning governs everything

The relative error in a computed inverse is bounded by roughly κ(A)·u, with κ the condition number and u the unit roundoff. For an ill-conditioned matrix the computed inverse can have no correct digits at all while still satisfying a small backward error. Two consequences a reader should carry away:

1. A small residual MMULT(A, MINVERSE(A)) − I does not certify the inverse. Backward stability gives you a small residual almost regardless of conditioning; it does not give you a small forward error.
2. Near-singular is a continuum, not a boundary. The #NUM! from MINVERSE fires at some threshold; just inside it, the function returns numbers that are formally defined and practically meaningless. The dangerous inputs are the ones that do not error.

Golub and Van Loan, Matrix Computations, section 3.5, and Higham chapter 14, are the standard treatments; the residual-versus-error distinction is Higham's chapter 7.

Rust code

50 minverse "MINVERSE" (1, 1) [R "array"] |s, a, sv| {
    let m = matrix(s, a, 0, sv)?;
    let inverse = k::matrix_family::inverse_kernel(&m).map_err(Exit::Error)?;
    out_matrix(s, &inverse)
}

pub(crate) fn inverse_kernel(matrix: &[Vec<f64>]) -> Result<Vec<Vec<f64>>, WorksheetErrorCode> {
    let n = matrix.len();
    if n == 0 || matrix.iter().any(|row| row.len() != n) {
        return Err(WorksheetErrorCode::Value);
    }

    // In-place LU: below the diagonal holds the unit-lower multipliers, on and above holds U.
    let mut a = matrix.to_vec();
    let mut piv: Vec<usize> = (0..n).collect();
    const EPS: f64 = 1e-12;

    for k in 0..n {
        let mut pivot_row = k;
        let mut pivot_abs = a[k][k].abs();
        for row in (k + 1)..n {
            let candidate = a[row][k].abs();
            if candidate > pivot_abs {
                pivot_abs = candidate;
                pivot_row = row;
            }
        }

        if pivot_abs < EPS {
            return Err(WorksheetErrorCode::Num);
        }

        if pivot_row != k {
            a.swap(pivot_row, k);
            piv.swap(pivot_row, k);
        }

        let pivot = a[k][k];
        for row in (k + 1)..n {
            let factor = excel_x87_div(a[row][k], pivot);
            a[row][k] = factor;
            for col in (k + 1)..n {
                let product = excel_x87_mul(factor, a[k][col]);
                a[row][col] = excel_x87_sub(a[row][col], product);
            }
        }
    }

    // Solve A·x = e_j for each column j of the inverse, using the stored LU and the row
    // permutation. Forward: L·y = P·e_j (unit lower). Back: U·x = y (plain division).
    let mut inverse = vec![vec![0.0_f64; n]; n];
    for j in 0..n {
        let mut y = vec![0.0_f64; n];
        for i in 0..n {
            let mut s = if piv[i] == j { 1.0 } else { 0.0 };
            for kk in 0..i {
                let product = excel_x87_mul(a[i][kk], y[kk]);
                s = excel_x87_sub(s, product);
            }
            y[i] = s;
        }

        let mut x = vec![0.0_f64; n];
        for i in (0..n).rev() {
            let mut s = y[i];
            for kk in (i + 1)..n {
                let product = excel_x87_mul(a[i][kk], x[kk]);
                s = excel_x87_sub(s, product);
            }
            x[i] = excel_x87_div(s, a[i][i]);
        }

        for (i, value) in x.into_iter().enumerate() {
            inverse[i][j] = if value == 0.0 { 0.0 } else { value };
        }
    }
    Ok(inverse)
}

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

• MMULT — the verifier. MMULT(A, MINVERSE(A)) against MUNIT is the residual test, and it is the single most useful thing a reader can do with these four functions together.
• MDETERM — shares the family's LU forward elimination in the reference engine, and is related mathematically by det(A⁻¹) = 1/det A. Sharing an elimination is not sharing a measurement: MDETERM's own evidence record forbids it from borrowing this page's figures, and this page does not lend them.
• MUNIT — the identity the inverse is defined against.
• LINEST / TREND / LOGEST — regression surfaces that solve linear systems internally. They are the reason most worksheets think they need MINVERSE, and they are usually the better tool, because they solve rather than invert.
• SUMPRODUCT with an explicit 2×2 formula — for the smallest cases, writing out 1/(ad-bc)·[d,-b;-c,a] is a legitimate alternative and is often more accurate than a general factorisation, because it performs fewer operations.

The most important relationship is a negative one: do not invert to solve. To compute A⁻¹b, solve Ax = b. Forming the inverse and multiplying is slower, less accurate, and needs a nonsingular matrix where a solve needs only a consistent system. Higham devotes a section to this (Accuracy and Stability of Numerical Algorithms, chapter 14, on the "fallacy" of matrix inversion); Excel's worksheet surface unfortunately makes the wrong route the easy one, since MINVERSE exists and a general solve does not.

Named variants

No named variants are recorded yet.