MDETERM Function in Microsoft Excel
Part 1: Introduce
Definition:
The MDETERM function in Microsoft Excel returns the matrix determinant of an array.
Purpose:
Matrix determinants are generally used for solving mathematical equation systems involving several variables.
Syntax & Arguments:
MDETERM(array)
Explain the Arguments in the function:
- Array: Required. A numeric array with an equal number of rows and columns. This array can be given as a cell range (e.g., A1:C3), as an array constant (e.g., {1,2,3;4,5,6;7,8,9}), or as a name to either of these.
Return value:
The matrix determinant is a number derived from the values in the array.
Remarks:
- MDETERM returns the
#VALUE!Error when:- Any cells in the array are empty or contain text.
- The array does not have an equal number of rows and columns.
- MDETERM is calculated with an accuracy of approximately 16 digits, which may lead to a small numeric error when the calculation is incomplete.
Part 2: Examples
🔹 Example 1:
Purpose of example: Determinant of a 3×3 matrix.
Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | 3 | 8 | 5 |
| 2 | 3 | 6 | 1 |
| 3 | 1 | 1 | 0 |
| =MDETERM(A1:C3) | Result | 88 |
Explanation:
This example calculates the determinant of a 3×3 matrix using the MDETERM function.
🔹 Example 2:
Purpose of example: Determinant of a matrix using array constants.
Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | 3 | 6 | 1 |
| 2 | 1 | 1 | 0 |
| 3 | 3 | 10 | 2 |
| =MDETERM({3,6,1;1,1,0;3,10,2}) | Result | 1 |
Explanation:
This example demonstrates using array constants directly in the MDETERM function to calculate the determinant.
🔹 Example 3:
Purpose of example: Determinant of a 2×2 matrix using array constants.
Data sheet and formulas:
| A | B | |
|---|---|---|
| 1 | 3 | 6 |
| 2 | 1 | 1 |
| =MDETERM({3,6;1,1}) | Result |
Explanation:
This example calculates the determinant of a 2×2 matrix using array constants.
🔹 Example 4:
Purpose of example: Error due to unequal rows and columns.
Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 1 | 3 | 8 | 5 |
| 2 | 1 | 3 | 6 | 1 |
| =MDETERM({1,3,8,5;1,3,6,1}) | Result | #VALUE! |
Explanation:
This example returns an error because the array does not have an equal number of rows and columns.
🔹 Example 5:
Purpose of example: Determinant of a 4×4 matrix.
Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 1 | 3 | 8 | 5 |
| 2 | 1 | 3 | 6 | 1 |
| 3 | 7 | 3 | 10 | 2 |
| =MDETERM(A1:D3) | Result | 88 |
Explanation:
This example calculates the determinant of a 4×4 matrix using the MDETERM function.
🔹 Example 6: Using MDETERM with IF
Purpose of example: Determine if the matrix determinant is positive or negative.
Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | 2 | 5 | 3 |
| 2 | 4 | 1 | 6 |
| 3 | 7 | 3 | 2 |
| =IF(MDETERM(A1:C3)>0, “Positive”, “Negative”) | Result | Positive |
Explanation:
This example checks if the determinant of the matrix is positive or negative using the IF function. If the determinant is positive, it returns “Positive”; otherwise, it returns “Negative”.
🔹 Example 7: Using MDETERM with SUM
Purpose of example: Sum the matrix determinant with another value.
Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | 3 | 2 | 4 |
| 2 | 5 | 6 | 1 |
| 3 | 8 | 7 | 3 |
| =SUM(MDETERM(A1:C3), 10) | Result | 52 |
Explanation:
This example calculates the determinant of the matrix and then adds 10 to the result using the SUM function.
🔹 Example 8: Using MDETERM with VLOOKUP
Purpose of example: Find the determinant value from a lookup table.
Data sheet and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 45 | “Matrix A” | |
| 2 | 4 | 5 | 6 | 60 | “Matrix B” | |
| 3 | 7 | 8 | 9 | 0 | “Matrix C” | |
| =VLOOKUP(MDETERM(A1:C3), E1:F3, 2, FALSE) | Result | Matrix A |
Explanation:
This example uses the VLOOKUP function to find the name of the matrix based on its determinant value from a lookup table.
🔹 Example 9: Using MDETERM with AVERAGE
Purpose of example: Average the matrix determinant with another set of numbers.
Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | 2 | 4 | 6 |
| 2 | 8 | 5 | 3 |
| 3 | 7 | 1 | 9 |
| =AVERAGE(MDETERM(A1:C3), 5, 10, 15) | Result | 8.75 |
Explanation:
This example calculates the average of the matrix determinant and three other numbers (5, 10, 15) using the AVERAGE function.
🔹 Example 10: Using MDETERM with MAX
Purpose of example: Find the maximum value between the matrix determinant and another set of numbers.
Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | 3 | 6 | 9 |
| 2 | 2 | 5 | 8 |
| 3 | 1 | 4 | 7 |
| =MAX(MDETERM(A1:C3), 10, 20, 30) | Result | 30 |
Explanation:
Using the MAX function, this example determines the maximum value between the matrix determinant and three other numbers (10, 20, 30).
🔹 Example 11: Using MDETERM with MIN
Purpose of example: Find the minimum value between the matrix determinant and another set of numbers.
Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | 1 | 3 | 5 |
| 2 | 7 | 9 | 2 |
| 3 | 4 | 6 | 8 |
| =MIN(MDETERM(A1:C3), 5, 10, 15) | Result | 5 |
Explanation:
Using the MIN function, this example determines the minimum value between the matrix determinant and three other numbers (5, 10, 15).
🔹 Example 12: Using MDETERM with ROUND
Purpose of example: Round the matrix determinant to the nearest whole number.
Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | 2 | 5 | 8 |
| 2 | 3 | 6 | 9 |
| 3 | 4 | 7 | 1 |
| =ROUND(MDETERM(A1:C3), 0) | Result | 27 |
Explanation:
This example rounds the determinant of the matrix to the nearest whole number using the ROUND function.
Part 3: Tips and tricks
- Ensure that the array you provide has an equal number of rows and columns to avoid the
#VALUE!error. - You can use cell ranges, array constants, or names to provide the array to the MDETERM function.
- Remember that the matrix determinant is a single number derived from the values in the array, and it’s used for solving systems of mathematical equations.