[red_eyed]

I am attempting to rank multiple "lists" that I have placed into columns in excel (up to 30 columns / lists). Each list has 10 items placed in order via Excel Rows.

For example, Column 1 shows Joe likes 1. Apples, 2. Bananas, 3. Pears 4. Oranges while a second column lists Sues prefers 1. Pears, 2. Bananas, and 3. Apples 4. Oranges

Is there a formula that I am overlooking that calculates / ranks the popularity of the listed items?

Thanks!

 

[RSpecianjr]

Hey red eyed,

You would need a static list of the products first. After you have that you should be able to rank based on the sum of the ranks. It would looks something like this:

Apple - 1 + 3...
Bananas - 2 + 2...
Pears - 1 + 3...
Oranges - 4 + 4...
And so on...

Once you have one list with the overall sum of numbers, you could rank the totals based on place with other totals, using the rank formula. Smallest being highest ranked.

If you wanted to separate ties, something I often do is input an addition of the row*.0001 to the end of the total for say Apples. That way, instead of 4 you would get 4.0001 (Assuming row 1). Bananas would be 4.0002... giving it a slightly higher number so that it is ranked after Apples in rank 2.

It may be possible to have everything done in one formula, but it would be a crazy formula... Also, it is possible to have a macro do... i personally don't have time to write it, but if that is the way you wanted to go, im sure someone else on the forum could put it together.

Personally, the quickest/easiest way is going to be the first way i mentioned.

Hope this helps,

Robert D. Specian Jr.