Wednesday, December 28, 2016

Using Formulas To Create Arrays In Excel

In Excel 2016 one can use a constant such as {1,2,3,4;5,...} to define an array but functions provide an easier method. Below the numbers 1-16 are placed in order in the array M. Also, the two operators F and G are used to produce two more operators, H and E, by multiplication. The multiplication table for the set of four operators shows that it forms an Abelian group and so the order of the multiplication among themselves doesn't matter.

Left and right multiplication of F, G and H with M produces a set of 16 distinct rearrangements including the original. In FM all the columns are inverted and MF has its rows reversed. GM does a double shift upwards with replacement. In MG there's a double shift to the left. One can see the right multiplication produces different results left multiplication does.

To show how this works we can look at the action of an array with just one nonzero value which allows shift a row or column depending on the filled cell's location.

These are some of the operators kinds of operators which leave the sums in the magic square unchanged. The situation is a little more complicated than that of the dihedral group of a square with has only reflections and rotations.