Help me optimize a list in Excel.

September 28, 2006 1:33 PM

Help me optimize a list in Excel. (Excel gurus welcome!)

I have a group of 20 items. The first ten items go in List 1, the second ten go in List 2. Each item has 6 characteristics (expressed in numbers). I want List 1 and List 2 to be as similar as possible in terms of the characteristics of the items that make it up.

The way I'm doing this presently in Excel is by running a t-Test on each of the characteristics of the first ten numbers and the second ten numbers. The p-value the t-Test returns tells me how similar they are. The closer to 1 the value is, the more similar the Lists are. I'm repeatedly ordering the items randomly to get different orderings of all the items. The value of the t-Test in Excel changes dynamically with every ordering. Ultimately, I'm trying to find the optimum ordering (or at least a very good one) of the items so that the values returned by the t-Test are all very close to 1.

Right now, I'm just pressing sort on Column A (see attached image and Excel file) over and over until I get a good set of p-values from the t-Tests. There must be a programmatic way to do this though. Can anyone help me out?



The files in question here. To make it work, just repeatedly sort Col A.

This whole setup is hard to explain, so please leave me any questions you have and I'll answer them as best I can.

Thanks!

posted by pealco to computers & internet (3 comments total)

Oops, the link the the Excel file is wrong. Here is the right one.
posted by pealco at 1:34 PM on September 28, 2006

I believe the hungarian algorithm can be applied to this. I can't explain how to do this automatically in Excel, but the hungarian algorithm goes as follows:

1. Create an N*N table where N is the number of words you have. The words go along the top and down the side of the table.

2. Apply the T-test in every cell of the table (comparing the 2 words).

3. Do New Value = 1 - Old Value for every cell in the table.

4. You should have a diagonal line of 0s now, where you are comparing a word with itself. Replace these with 2s.

5. For every row, subtract the smallest number in that row from all of the numbers in that row (if the smallest number is 0, do nothing).

6. For every column, subtract the smallest number in that column from all of the numbers in that column (if the smallest number is 0, do nothing).

7. Draw on the smallest number of vertical or horizontal lines possible to cover all of the 0's in the table.
~If the number of lines = n, go to step 9.
~If the number of lines < n, go to step 8.br>
8. Select the smallest entirely uncovered number (not covered by the lines added in step 7). Subtract it from all of the uncovered numbers. Also, add this number to every other number that is covered by both a horizontal and vertical line. Then go back to step 7.

9. Now make pairings by picking pairs of words where their value in the table is 0.

I know this is a long algorithm, but it will give you the best possible pairings. There may be better/easier to implement ones, but this is the only one I know that can do this.
posted by spark at 3:17 PM on September 28, 2006

If you don't want to do it spark's way, ask the guys on the forum at Mr. Excel.
posted by bingo at 5:04 AM on September 29, 2006