Excel - Determine Parity of Permutation

Question

I am working on an Excel sheet where I need to determine the parity of a vertical array of numbers of size N. The array contains each number from 1 to N exactly one time each.

In this context, parity is defined as how many swaps is necessary to convert the scrambled array to a sorted array from smallest to largest.

For example, the array {3;1;2;4} has even parity because it would require two swaps (at minimum) to convert to {1;2;3;4}, but would always require an even number of swaps. See below.

3   -->   1         1
1   -->   3   -->   2
2         2   -->   3
4         4         4

Another example: {2;1;4;5;3} has odd parity because it would require three swaps (at minimum) to convert to {1;2;3;4;5}, but would always require an odd number of swaps. See below.

2   -->   1         1         1
1   -->   2         2         2
4         4   -->   3         3
5         5         5   -->   4
3         3   -->   4   -->   5

I am looking for a solution that would return TRUE for arrays with even parity and FALSE for arrays with odd parity. (And I don't care what the result is for arrays that do not contain all numbers from 1 to N because I have other checks in the spreadsheet to handle those cases.)

I have figured out a solution that uses several helper columns, but it seems like a sluggish calculation.

I do this by checking if each individual number in the array is in the correct index and, if it isn't perform a swap. I then sum up the amount of swaps that occurred and use MOD(<swaps>,2)=0 to determine if it is even parity.

See below for an example calculation with the array {8;5;3;2;4;1;7;6}.

I've color coded the cells to easily tell what is happening:

White = reference array {1;2;3;4;5;6;7;8}

Blue = input array

Grey = "helper" arrays where each successive column performs a swap if necessary

Red = Indicates if a swap occurred from previous column

Green = 1 if swap occurred in that column, and 0 if a swap didn't occur

Yellow = Sum of all green cells, effectively telling how many swaps occurred.

In this example, since the yellow cell is 4, which is an even number, the input array has even parity.

The question is: Can this calculation be done more efficiently in Excel without VBA? I'm not necessarily against helper columns, but again it just seems that my solution is sluggish and I'm wondering if there is a better way.

Answer 1

I think I have a way of doing this without the helper columns!

First, I'll show you a way with N helper columns and then I'll show how to use array formulas instead. Consider the matrix show below:

The green ranges are the indices and the blue range is your permutation. The yellow matrix is your permutation matrix defined as seen in the formula box.

The parity of your permutation is the same as the value of the determinant of this matrix!

Luckily, Excel has a built-in determinant function MDETERM(). An even permutation has parity 1 and an odd permutation has parity -1, so you can get the determinant simply with the formula

=MDETERM(C2:J9)

Now this is pretty cool, but the real kicker is that we don't even need to make that matrix. We can construct it in an array formula like this instead:

{=MDETERM(IF(B2:B9=TRANSPOSE(A2:A9),1,0))}

Here we only use columns A and B! Columns C:J are unused in this version.

(Note that this is an array formula so you will need to use Ctrl+Shift+Enter to validate it. This will wrap the braces around the formula. Don't do that manually.)