MDETERM

What it does

MDETERM(array)

MDETERM(array) returns the determinant of a square matrix.

The determinant is the unique function of a square matrix that is multilinear in the rows, alternating (it changes sign when two rows are swapped, and vanishes when two rows are equal), and equal to 1 on the identity. That characterisation determines it completely, and it yields the Leibniz formula

det A = Σ_{σ ∈ Sₙ} sgn(σ) · Π_{i=1..n} a_{i,σ(i)}

a sum over all n! permutations. Microsoft's page gives the 3×3 instance of the same thing by cofactor expansion:

MDETERM(A1:C3) equals A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)

What it means. det A is the signed volume scaling factor of the linear map A: it multiplies oriented n-volumes, and its sign records whether orientation is preserved. Hence the two facts every reader needs:

• det A = 0 exactly when A is singular — the map collapses space, the columns are linearly dependent, no inverse exists.
• det(AB) = det A · det B, and det(Aᵀ) = det A, and det(cA) = cⁿ · det A for an n×n matrix.

Further properties worth having: det of a triangular matrix is the product of its diagonal; det of a permutation matrix is its sign; det(A⁻¹) = 1/det A. The last connects this page directly to MINVERSE, and the family's shared implementation exploits the same connection from the other side — the LU factorisation that inverts a matrix also hands you its determinant as the product of the pivots.

Domain and range. Domain: square numeric matrices. Range: all reals (any real is the determinant of some matrix). The function is a polynomial in the entries, hence continuous and infinitely differentiable — there are no poles and no branch cuts. Every difficulty on this page is numerical, not analytic.

Arguments

Exactly one argument; the declared arity is one to one. The array may be given as a cell range, an array constant, or a name.

The argument preparation profile declared for this function makes references visible to the adapter rather than resolving them to values first, which is the shape one expects of a function that consumes a whole grid rather than a scalar.

Try it

Quirks

Result and edge cases

Returns Number.

• A 1×1 matrix — a bare scalar — returns that scalar. The determinant of a 1×1 matrix is its single entry, so this is right, and it is also the degenerate case where the matrix machinery disappears entirely.
• A logical argument produces #VALUE!, and numeric text produces #VALUE!. This is the family's distinguishing coercion behaviour: unlike the scalar math functions in this category, which convert logicals and numeric text happily, the matrix family refuses them. Microsoft's page documents the refusal for text ("Any cells in array are empty or contain text") but says nothing about logicals.
• An inline 2×2 array returns the determinant, and that specific value is one of the four pinned by the structural COM probe recorded in this page's second evidence record.
• An empty range produces #VALUE!, consistent with the documented empty-cell rule.
• The largest finite double as a 1×1 matrix is returned unchanged.

The battery contains no non-square case, no singular case beyond the trivial, and no matrix above 2×2, so it does not exercise the elimination at all.

Errors

Error values in the array propagate under the shared discipline (coercion and lifting); the family declares a reduction error-collapse profile, meaning competing errors inside the grid are folded rather than each surfacing independently.

Microsoft's page documents no #NUM! for MDETERM. That is worth contrasting with MINVERSE, which does document #NUM! for a singular matrix — a determinant of zero is a perfectly good answer, whereas an inverse of a singular matrix does not exist. The asymmetry is mathematically correct and is a useful check on any implementation: MDETERM should never refuse a well-formed square numeric matrix.

A note on the accuracy remark. Microsoft's page states:

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

Read carefully, this is not an error bound. "Approximately 16 digits" describes binary64 precision generally, not this function's accuracy on any particular matrix, and "when the calculation is not complete" names no determinable condition. The genuine statement — that the relative error in a computed determinant is governed by the conditioning of the matrix and can be arbitrarily large — is in "Numerical notes" below, and is considerably less reassuring than the documentation.

How to implement

Numerical notes

Never compute the determinant by its definition

The Leibniz sum has n! terms; cofactor expansion is the same cost. For a 10×10 matrix that is over three million products, for 15×15 it is over a trillion. Every practical implementation instead factors the matrix — LU with partial pivoting, O(n³) — and takes

det A = (−1)^(number of row interchanges) · Π pivots

The determinant overflows and underflows long before the matrix is remarkable

Because det scales as cⁿ, a 20×20 matrix with entries of order 100 has a determinant of order 10⁴⁰, and one with entries of order 0.01 has a determinant of order 10⁻⁴⁰. Overflow and underflow are reached at modest sizes with entirely ordinary data. The standard remedy in numerical libraries is to return log|det| and a sign rather than the determinant itself (LAPACK-based codes and R's determinant() do this), which stays in range for any matrix the factorisation can handle. Excel publishes no such surface, so MDETERM inherits the full dynamic-range problem. For a large matrix MDETERM may return zero, or an overflow condition, for a matrix that is perfectly well conditioned.

The determinant is a bad test for singularity

This is the single most important practical fact on the page, and it is not in any documentation.

• A matrix can have a tiny determinant and be perfectly well conditioned: 0.01·I on 20×20 has a determinant of 10⁻⁴⁰ and a condition number of exactly 1. Nothing is wrong with it.
• A matrix can have a determinant of 1 and be catastrophically ill conditioned: any unimodular matrix with wildly different singular values.

The determinant is not scale-invariant and does not measure distance to singularity; the condition number does. The classic statements of this are in Forsythe and Moler, Computer Solutions of Linear Algebraic Systems, and in Higham, Accuracy and Stability of Numerical Algorithms, chapter 14, both of which say plainly that det should not be used to detect near-singularity. A worksheet that tests IF(MDETERM(A)=0, …) before calling MINVERSE is doing something that looks careful and is not.

Error growth

The computed determinant from LU with partial pivoting satisfies a backward-error result: it is the exact determinant of a nearby matrix. The forward error — how far the number is from the true determinant — is bounded by roughly the condition number times the unit roundoff times n, so it grows with both size and conditioning. For an ill-conditioned matrix the leading digits of MDETERM's answer can be wrong, and the documentation's "approximately 16 digits" does not warn about it. Higham chapter 14 gives the analysis; Golub and Van Loan, Matrix Computations, section 3.2, gives the pivoting theory.

Rust code

49 mdeterm "MDETERM" (1, 1) [R "array"] |s, a, sv| {
    let m = matrix(s, a, 0, sv)?;
    out(s, k::matrix_family::determinant_kernel(&m))
}

pub(crate) fn determinant_kernel(matrix: &[Vec<f64>]) -> Result<f64, WorksheetErrorCode> {
    let n = matrix.len();
    if n == 0 || matrix.iter().any(|row| row.len() != n) {
        return Err(WorksheetErrorCode::Value);
    }
    let mut work = matrix.to_vec();
    let mut swaps = 0usize;
    let mut det = 1.0;
    const EPS: f64 = 1e-12;

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

        if pivot_abs < EPS {
            return Ok(0.0);
        }

        if pivot_row != pivot_idx {
            work.swap(pivot_row, pivot_idx);
            swaps += 1;
        }

        let pivot = work[pivot_idx][pivot_idx];
        det *= pivot;
        for row in (pivot_idx + 1)..n {
            let factor = work[row][pivot_idx] / pivot;
            for col in pivot_idx..n {
                work[row][col] -= factor * work[pivot_idx][col];
            }
        }
    }

    if swaps % 2 == 1 {
        det = -det;
    }
    Ok(det)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• MMULT — det(AB) = det A · det B gives the cheapest available metamorphic test for all three functions at once, needing no oracle table.
• MUNIT — det(I) = 1, the normalisation in the determinant's defining characterisation.
• SUMPRODUCT — for a 2×2 or 3×3 matrix the determinant is a small explicit expression, and writing it out is often more accurate than calling MDETERM, because it avoids the pivoting and the divisions. Microsoft's own 3×3 formula is the expression to use.
• LINEST, TREND, and the regression family — they solve normal equations whose singularity MDETERM is sometimes (wrongly) used to test. See the warning below.

Named variants

No named variants are recorded yet.