A to Z of Excel Functions: The MDETERM Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the MDETERM function.

The MDETERM function

In mathematics, especially in areas such as linear algebra, matrices may be used to solve simultaneous equations. For the record, a matrix is not just a movie it’s a rectangular arrangement of mn elements, in the dimensions of m rows by n columns, e.g. a matrix A (say) may be represented as

It is often written in compact form as

Should the matrix be square (i.e. m =  n), then we may calculate a scalar value, known as the determinant which can be used to calculate matrix inverses, solve systems of linear equations and assist with calculus (unless it’s really stuck on your teeth).  For a square matrix A, this determinant is denoted by

For example, with a 3 x 3 matrix, the determinant may be represented as

Some texts state that if the determinant is zero, the matrix inverse does not exist, but this is not strictly correct, as there may be another multiplicative identity for such a matrix – but that’s way beyond what we wish to talk about here!

To calculate it, the idea is as follows. For a 2 x 2 matrix,

For a 3 x 3 matrix, the calculation extends:

In this situation, each determinant of the three 2 × 2 matrices is called a minor of the matrix A.  This procedure can be extended to give a recursive definition for the determinant of an n × n matrix, known as a Laplace expansion.  It sounds a bit like my waistline.

The Excel function MDETERM returns the matrix determinant of an array.  It has the following syntax:

where:

• array is required, and represents a numerical array with an equal number of rows and columns.

It should be noted that:

• array may be given as:

o   a cell range, e.g. A1:C3

o   an array constant, such as {1,2,3;4,5,6;7,8,9}

o   a name to either of these

• MDETERM returns the #VALUE! error when:

o   any cells in array are empty or contain text

o   array does not have an equal number of rows and columns

• the matrix determinant is a number derived from the values in the array.  For a three-row, three-column array, A1:C3, the determinant is defined as:

MDETERM(A1:C3) equals

• matrix determinants are generally used for solving systems of mathematical equations that involve several variables

• 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.

As an example:

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.