Excel MDETERM Function: The Determinant of a Matrix
Returns the determinant of a square array of numbers.
MDETERM computes the determinant of a square matrix — a single number derived from all its values. If that sentence means nothing to you, this function is almost certainly not one you need; it exists for linear algebra rather than for business reporting.
Where it does earn its place is checking whether a system of simultaneous equations has a unique solution. A determinant of zero means the matrix is singular, the equations are not independent, and MINVERSE will fail on it. Testing the determinant first is the standard way to avoid that error.
The array must be square: the same number of rows as columns. That is the constraint people hit first, because a range selected by dragging is rarely square by accident.
Syntax
=MDETERM(array)Arguments
arrayRequired- A square range or array constant. Two by two, three by three, and so on — anything else errors.
The example data
A 3×3 matrix in A2:C4, with a fourth row that makes A2:C5 non-square.
| A | B | C | |
|---|---|---|---|
| 1 | Col 1 | Col 2 | Col 3 |
| 2 | 3 | 1 | 2 |
| 3 | 4 | 6 | 5 |
| 4 | 7 | 8 | 9 |
| 5 | 2 | 2 | 4 |
Worked examples
=MDETERM(A2:C4)Result: -27
The determinant of the top three-by-three block. Non-zero, so the matrix is invertible.
=MDETERM(A2:B3)Result: 14
A two-by-two determinant, which is simply (3×6) − (1×4). Any square size works.
=MDETERM(A2:C5)Result: #VALUE!
Four rows against three columns is not square, and MDETERM refuses rather than guessing.
=IF(MDETERM(A2:C4) = 0, "Singular", "Invertible")Result: Invertible
The practical use: check before attempting MINVERSE, which errors on a singular matrix.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#VALUE!
Why it happens: The array is not square, or one of its cells is empty or contains text.
How to fix it: Count the rows and columns — they must match — and make sure every cell holds a number.
Result is a tiny number instead of zero
Why it happens: Floating-point arithmetic. A mathematically singular matrix can return something like 1.2E-16 rather than exactly 0.
How to fix it: Test with a tolerance: =ABS(MDETERM(range)) < 1E-10 rather than = 0.
#NUM!
Why it happens: The matrix is large enough that the determinant overflows.
How to fix it: MDETERM is limited to arrays of about 73 by 73. Larger problems need a different tool.
Tips worth knowing
- A determinant of zero means MINVERSE will fail — check MDETERM first.
- Compare against a small tolerance rather than exactly zero, because of floating point.
- MMULT multiplies matrices and MINVERSE inverts them; the three are used together for solving linear systems.
- If you are not solving simultaneous equations, you almost certainly want a different function.
Frequently asked questions
What is a determinant used for?
Mostly for testing whether a matrix can be inverted, which in turn decides whether a system of simultaneous equations has a single solution. A determinant of zero means it does not, and MINVERSE will error.
Why does MDETERM return #VALUE!?
Almost always because the range is not square — it needs exactly as many rows as columns. Blank cells or text inside the range cause the same error.
Why is the result 3.6E-16 instead of 0?
Floating-point rounding. A matrix that is mathematically singular often computes to a very small number rather than exactly zero, so test with =ABS(MDETERM(range)) < 1E-10 instead of comparing to 0.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.