MDETERM Function
Returns the determinant of a square numeric matrix. Use it to check whether a matrix can be inverted before moving on to MINVERSE.
Summary
MDETERM returns the determinant of a square matrix. In Excel terms, that means you give it a square block of numbers like a 2x2 or 3x3 range, and it gives you one number back.
That number is mainly useful as a checkpoint. If the determinant is 0, the matrix cannot be inverted, so formulas like MINVERSE will fail. That is why MDETERM is often the first thing to run when you are building a matrix model.
Purpose
Helps you test whether a square matrix is suitable for inverse-based calculations.
Return Value
The result is a single numeric value, even when the input range is a larger square matrix.
Syntax
=MDETERM(array)The array must be numeric and square. Microsoft notes that MDETERM returns #VALUE! if the range contains blanks or text, or if the number of rows and columns does not match.
Arguments
- array - Required. A square numeric range, array constant, or named array.
MDETERM vs Other Matrix Functions
These matrix functions work together, but they do different jobs. MDETERM is the quick check. The others do the heavier matrix operations after that.
| Function | Main job | Returns | Use it when |
|---|---|---|---|
MDETERM |
Gets the determinant | One number | You want to test whether a matrix is invertible |
MINVERSE |
Gets the inverse matrix | An array | You need the inverse of a square matrix |
MMULT |
Multiplies matrices | An array | You need a matrix product |
MUNIT |
Builds an identity matrix | An array | You need a neutral matrix for testing or setup |
Using MDETERM
The most common use is simple. Run MDETERM before MINVERSE. If the result is 0, stop there because the matrix does not have an inverse. That saves you from chasing a #NUM! error later.
It also helps when you want to understand the matrix itself a little better. In many practical Excel models, you do not need to calculate the determinant by hand. You just need to know whether it is zero, non-zero, or so close to zero that the matrix is unstable. Microsoft also notes that the function is calculated with about 16 digits of accuracy, so a singular matrix can sometimes show a tiny leftover like 1E-16 instead of a perfect 0.
Example 1 - Find a 2x2 determinant
This is the simplest version. You point to a square 2x2 range and Excel returns one value.
=MDETERM(B2:C3)Calculate the determinant of a 2x2 matrix in B2:C3. Formula: =MDETERM(B2:C3).
Example 2 - Check a 3x3 matrix
The formula does not change when the matrix gets bigger. As long as the range stays square, MDETERM can evaluate it.
=MDETERM(B2:D4)Find the determinant for the 3x3 array in B2:D4. Formula: =MDETERM(B2:D4).
Example 3 - Test invertibility
If the determinant is not 0, the matrix can be inverted. That makes this a practical gate before using MINVERSE.
=MDETERM(B2:C3)<>0Check if the matrix in B2:C3 is invertible (Determinant <> 0). Formula: =MDETERM(B2:C3)<>0.
Example 4 - Use the determinant in another step
Because the result is a single number, you can scale it, compare it, or plug it into another formula without extra handling.
=MDETERM(B2:C3)*10Multiply the determinant of B2:C3 by 10. Formula: =MDETERM(B2:C3) * 10.
Conclusion Recap
- Main job:
MDETERMreturns the determinant of a square numeric matrix. - Best use: Check whether a matrix can be inverted before using
MINVERSE. - Important rule: The input must be square and numeric.
- Error to expect:
#VALUE!appears when the range is not square or contains blanks or text. - Precision note: A result that should be zero can sometimes appear as a very small number because of floating-point rounding.
Tell your friends about this post