MDETERM gives Err:502 on non-contiguous range of cells

Question

I have the following setup:

A 3x3 matrix in the cells A1:C3 and a 3x1 vector in D1:D3.

I would like to calculate the determinant of a new matrix formed by the 1st column of the original matrix, the vector and the 3rd column of the original matrix.

The formula I enter is: =MDETERM(A1:A3:D1:D3:C1:C3)

I get Err:502, but the data I am using has a determinant, I have verified it by placing the cells onto a different sheet and calculated the determinant of the contiguous range.

If you need data to reproduce, use a 3x3 identity matrix (in the columns A, B, C), whose determinant is 1, and then replace the middle column by a 3x1 vector (column D, whose content is actually the same as the middle column of the identity matrix, thus the whole range results in a matrix whose determinant is known to be 1.)

New 3x3 matrix formed by the colums A, D, C (in this order).

Answer 1

The range syntax you're using doesn't do what you think it does. Consider the case where you have an identity matrix in A1:C3. The syntax A1:A3:C1:C3:B1:B3 does not refer to the matrix in A1:C3 with the second and third column swapped. It simply refers to A1:C3. Excel or LibreOffice or whatever other spreadsheet program sees this range definition and just looks for the top-left and bottom-right corners of the range in all that mess. It doesn't take order into account. It's just defining a rectangular range.

You can check this by putting the identity matrix in A1:C3 and the matrix with swapped second and third columns in E1:G3. Check the determinants.

=MDETERM(A1:C3)

returns 1.

=MDETERM(A1:A3:C1:C3:B1:B3)

also returns 1.

But

=MDETERM(E1:G3)

returns -1.

In your particular case where the ranges you're trying to shuffle aren't contiguous, the syntax A1:A3:D1:D3:C1:C3 is read as A1:D3. This is not a square matrix, so MDETERM returns an error.

If you want to find the determinant of that noncontiguous range, you'll have to recreate the matrix somewhere else on the sheet.