MUNIT

What it does

MUNIT(dimension)

MUNIT(dimension) returns the identity matrix of the given size: the square matrix with ones on the main diagonal and zeros everywhere else.

Iₙ = (δᵢⱼ),   δᵢⱼ = 1 if i = j, else 0

δ is the Kronecker delta, and MUNIT is simply its tabulation over an n×n grid.

The identity is the multiplicative unit of the matrix algebra, and every property a reader needs follows from that:

• AI = IA = A for every conformable A. This is the defining property, and it is the reason MUNIT is useful in a worksheet at all.
• I⁻¹ = I, Iᵀ = I, I² = I — the identity is its own inverse, its own transpose, and idempotent.
• det I = 1 (this is the normalisation in the determinant's defining characterisation — see MDETERM), trace I = n, and every eigenvalue is 1 with the whole space as eigenspace.
• Iₙ is the unique matrix with these properties at each size, so MUNIT has exactly one correct answer per argument and no implementation freedom whatever.

Domain and range. Domain: the positive integers. Range: the identity matrices. This is the only member of the matrix family that is a generator rather than an operator — it consumes a scalar and produces a grid — which puts it closer in spirit to SEQUENCE and RANDARRAY than to its three siblings.

Arguments

Microsoft's page adds two facts in the same sentence: "It returns an array" and "The dimension has to be greater than zero."

Exactly one argument; the declared arity is one to one.

Result shape and entry. The result is a dimension × dimension array. Microsoft's page gives the mechanics: in current Microsoft 365 the formula spills from a single cell; in earlier versions the output range must be selected first and the formula confirmed with Ctrl+Shift+Enter.

Try it

Quirks

Result and edge cases

Returns Array of Number.

• Zero and negative dimensions produce #VALUE!, exactly as documented.
• A subnormal positive dimension produces #VALUE! — it truncates to zero, and zero is rejected. Consistent.
• The largest finite double produces #VALUE!. A dimension that large cannot be materialised; the rejection is sensible and is not documented.
• A logical argument is accepted, producing a 1×1 identity, and numeric text is accepted and truncated, producing the identity at the truncated size. This is a striking contrast with the rest of the family: MDETERM, MINVERSE and MMULT all refuse logicals and numeric text, while MUNIT converts them like an ordinary scalar math function. The four surfaces share one implementation module and do not share one coercion policy. That is a real finding about the family's internal consistency, and it is worth checking against Excel because there is no documented reason for the split.

Errors

That is the whole of the documented error surface. Note that it is #VALUE! and not #NUM!, which is unusual — a dimension of −1 is a domain failure, and #NUM! is Excel's conventional answer for those. MUNIT uses the kind-failure error for a domain failure, and the documentation is explicit about it, so this is one of the rarer cases where the documented behaviour is both surprising and unambiguous.

How to implement

Numerical notes

MUNIT has no numerical content. Both 0 and 1 are exactly representable in binary64, there is no arithmetic, and there is exactly one correct answer for each input. It cannot lose precision, cannot overflow, and admits no algorithmic variation.

That is precisely what makes it valuable, and it is worth saying why explicitly:

MUNIT is the family's exact anchor. Every other member has an accumulation order, a pivot choice, or a division that puts its last bits in question. MUNIT has none. So in any test of this family, MUNIT is the term that is known to be right, and any discrepancy in a residual MMULT(A, MINVERSE(A)) − MUNIT(n) is attributable entirely to the other two surfaces. A test design that instead builds its identity by hand, or by SEQUENCE comparison, gives up that certainty for nothing.

The two genuine engineering considerations are about size, not accuracy:

2. The identity should rarely be materialised at all. In numerical practice one applies the identity implicitly; forming it and multiplying by it costs O(n³) to achieve nothing. That is a remark about linear-algebra practice (Golub and Van Loan, Matrix Computations, chapter 1) rather than about Excel, but a worksheet that computes MMULT(A, MUNIT(n)) in production is doing the same wasteful thing.

Rust code

52 munit "MUNIT" (1, 1) [V "dimension"] |s, a, sv| {
    let n = num(s, a, 0)?.trunc();
    if !(1.0..=1_048_576.0).contains(&n) {
        return fail(WorksheetErrorCode::Value);
    }
    let size = n as usize;
    if size > 4096 {
        // A spill of more than 16M cells is not modelled.
        return Err(s.not_modelled(Some(0)));
    }
    let rows: Vec<Vec<f64>> = (0..size)
        .map(|r| (0..size).map(|c| if r == c { 1.0 } else { 0.0 }).collect())
        .collect();
    out_matrix(s, &rows)
}

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 — the operation MUNIT is the unit of. MMULT(A, MUNIT(n)) should return A entry for entry, and because every dot product involved is a sum of exact zeros and one exact copy, any deviation is a defect rather than a rounding difference. This makes MUNIT the family's calibration standard.
• MINVERSE — MMULT(A, MINVERSE(A)) against MUNIT(n) is the residual test. Read the caution on the MINVERSE page: a small residual does not certify an accurate inverse.
• MDETERM — det(MUNIT(n)) = 1 for every n, an exactness check that costs one cell.
• SEQUENCE — the other pure generator in the modern surface. MUNIT(n) is expressible as --(SEQUENCE(n)=TRANSPOSE(SEQUENCE(n))), which is worth knowing both as a fallback and as an independent implementation to compare against.
• IDENTITY does not exist in Excel; MUNIT is the name, and it is easy to miss when searching.

Named variants

No named variants are recorded yet.