This is a brief tutorial showing how to calculate the determinant of a matrix and the inverse of a matrix using two special spreadsheet functions MDETERM() and MINVERSE().
[sociallocker]
[/sociallocker]
MDETERM() & MINVERSE() – two important Excel spreadsheet functions for matrix manipulations
by George Lungu
– Using Excel built-in matrix operation functions might improve calculation
efficiency but it definitely makes model development and verification easier by
decreasing the complexity and number of formulas.
-This tutorial will briefly introduce the reader to two different spreadsheet
formulas for matrix manipulations:
=> MDETERM() – used to calculate the determinant of a matrix
=> MINVERSE() – used to calculate the inverse of a square matrix
– These functions are very useful for solving systems of linear equations in Excel
<excelunusual.com> by George Lungu
The MDETERM() function:
– Returns the determinant of a square matrix. You can read more about determinants on Wikipedia website. The
determinants are used to calculate solutions of linear systems of equations using Cramer’s rule. MDETERM is
calculated with an accuracy of approximately 16 digits, which may lead to a small numeric error when the calculation
is not complete. For example, the determinant of a singular matrix may differ from zero by 1E-16.
Syntax: MDETERM(array)
Spreadsheet implementation of MDETERM() function:
– Name a worksheet “Determinant” (in our case it is the third worksheet)
– In the worksheet “Determinant” create three arrays a 6×6 array, a 4×4
and a 2×2.
– Write the three arrays of numbers like in the snapshot to the left or
choose your own values.
– Calculate the determinant of matrix {A}:
G10: “=MDETERM(B3:G8)”
– Calculate the determinant of matrix {B}:
E19: “=MDETERM(B14:E17)”
– Calculate the determinant of matrix {C}:
C25: “=MDETERM(B22:C23)”
– You can verify the correctness of the formula by calculating the
determinant by hand. In the case of matrix {C} the result is obviously right
just by visual inspection.
<excelunusual.com> 2
The MINVERSE() function:
– Returns the inverse matrix of a square matrix stored in an array. You can read more about inversion of matrices and
inversion algorithms on Wikipedia website. The inverse of a matrix is the easiest way to calculate the solution of a
linear system of equations. MINVERSE() is calculated with an accuracy of approximately 16 digits, which may lead to
a small numeric error when the cancellation is not complete. Some square matrices cannot be inverted and will return
the #NUM! error value with MINVERSE(). The determinant for a noninvertible matrix is 0.
Syntax: MINVERSE(array)
Spreadsheet implementation of MINVERSE() function:
– Name a worksheet “Inverse” (in our case it is the fourth worksheet)
– In the worksheet “Inverse” create a 6×6 array in range B3:G8.
– Write the numbers of array A like in the snapshot to the left or
choose your own values.
– Calculate the inverse of matrix {A}:
D12: “=MINVERSE(B3:G8)”
– After plugging in the formula and hitting return, highlight the range
D12:I17 and holding down F2 press Ctrl+Shift+Enter. After that, the
inverse of matrix A will appear in the range D12:I17
– In general you can insert the start formula (in the current case cell
D12) in any of the four corners of the future range for the result array.
– The product between matrix A and its inverse is further calculated in
the range F20:K25 using the MMULT() function. The same product in the reverse order is
calculated in the range F28:K33. In both cases the result is what we expect: the identity matrix.
The end.
by George Lungu <excelunusual.com>