# Minimizing numbers along diagonal

Please forgive my ignorance if this isn't the appropriate place to ask this question, I'm by no means an expert in statistics.

I'll omit most of the esoteric linguistic details here, but I've run up against a roadblock using Excel, where I am trying to minimize the values along the diagonal, (top left to bottom right.) I would prefer smallest numbers to be near the diagonal, and larger numbers to be farthest away from it.

There are only two constraints;

(1) Individual cells may not move, only rows and columns; i.e. the relationship between all values along the a row or column must be maintained with any move.

(2) For every shift in a row there has to be a symmetric movement in columns. e.g. if row 3 is moved down three, column C must be moved right by three as well.

I'm open to using any kind of software and reformatting my data in any way, I'll post some photos below to give some clues as to what kind of operations I am hoping to perform. It is possible that the minum values are already closest to the diagonal in the second photo, but I want to be able to statistically prove that the numbers cannot be any lower.

The first graph is a screenshot of the raw data; it shows the similarity (lower numbers are more similar) between letters in the pre-classical Arabic language. The data is in alphabetical order, but for my purpose this order is arbitrary.

The second is my attempt at organizing based on some educated guesses at how the phonology could have been; in this design the letters that are closest to each other are more similar to each other, and thus have lower numbers.

• There are some algorithms discussed here that may be of some use to you. – Glen_b -Reinstate Monica Dec 2 '14 at 12:44

• Sorry did not mean to make it too complicated @CayetanoGonçalves. Learning a stats package is generally helpful. Checking the row and column sums is easy in Excel, every other suggestion would likely be annoying in Excel although probably possible with a little elbow grease. Whuber's suggestion just takes changing the data from a contingency table to a set of pairs, e.g. 46,88 in one row, 84,86 in the next etc. – Andy W Dec 1 '14 at 19:05