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.

Thanks in advance

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.

Raw data, in alphabetical order

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.

enter image description here

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

Your second example looks to me like you did a pretty good job (so I wouldn't expect to improve upon it much). Your constraint 1 is pretty typically, but I've never seen constraint 2 before, so you may need a novel solution. See the references for some examples of ordering tables, it may be possible to estimate the hierarchical clustering solutions for the rows and columns and then cut the dendrogram in an arbitrary way to suit your needs. (Or there may be other heuristic solutions based on PCA that will work for your example.) Although those don't focus specifically on the diagonal.

A simple technique is to order by row and/or column sums, so just see for your example if there are any rows/columns that stick out. That is easy enough to do in excel ;)

Also I would avoid the red-green color scale. Stephen Few has some applicable suggestions here. With only 5 values you may ditch the numbers all together and use a sequential color ramp.


  • 3
    $\begingroup$ (+1) Constraints (1) and (2) together merely ask for a permutation of the letters. An ordering is sought which places groups of similar letters near each other. Thus, you certainly have seen this situation before: it can be considered a clustering problem or a one-dimensional MDS. $\endgroup$ – whuber Dec 1 '14 at 18:23
  • $\begingroup$ Ah yes - good call @whuber. Makes evaluating the potential permutations and defining the "best" solution (however you want to define best) a much simpler task! $\endgroup$ – Andy W Dec 1 '14 at 18:38
  • $\begingroup$ Thanks for the information, I am trying to digest it. Would you two recommend sticking with Excel, or is there some sort of software package that would be easier? $\endgroup$ – Ryan Ward Dec 1 '14 at 18:40
  • 1
    $\begingroup$ 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. $\endgroup$ – Andy W Dec 1 '14 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.