Excel sort for relative placement
December 17, 2010 7:11 AM Subscribe
I'm trying to implement a sorting algorithm in Excel known as "relative placement." Here's the setup:
1. I will have an odd number of judges ranking n couples 1-n.
2. In order to receive a ranking, a couple needs a majority of judges votes (e.g., if one couple received 3 1st rankings from a group of 5 judges, they would receive the first place ranking).
3. If no couple has a majority of votes, then you count the votes for ranks 1-2 (if still no majority, then first through third, through fourth, etc.)
4. In the case of a tie (e.g., out of 5 judges, two couples were ranked as either first or second by at least three judges), the total ranks are added to break the tie (so a couple that had first, first, second would have a total of 4, a couple with second, second, first would have a total of 5, therefore the first couple gets first and the second couple receives 2nd).
5. If there is still a tie when adding total ranks, the comparison for the tied couples only goes on by extending the rankings included (so if the couples both had a majority with tied total ranks after first and second place votes, now they would compare those couples on 1st, 2nd, and 3rd place votes).
6. Keep going until all the couples are ranked.
A fuller description of the procedure and an example case can be viewed at http://www.swingdancecouncil.com/library/relativeplacement.htm. I'm looking at Parts C and D (Final Placements and Example).
(If there are any inconsistencies between my description above and the details on that page, assume that I screwed up. Sorry!)
Is there any way to do this in Excel?
1. I will have an odd number of judges ranking n couples 1-n.
2. In order to receive a ranking, a couple needs a majority of judges votes (e.g., if one couple received 3 1st rankings from a group of 5 judges, they would receive the first place ranking).
3. If no couple has a majority of votes, then you count the votes for ranks 1-2 (if still no majority, then first through third, through fourth, etc.)
4. In the case of a tie (e.g., out of 5 judges, two couples were ranked as either first or second by at least three judges), the total ranks are added to break the tie (so a couple that had first, first, second would have a total of 4, a couple with second, second, first would have a total of 5, therefore the first couple gets first and the second couple receives 2nd).
5. If there is still a tie when adding total ranks, the comparison for the tied couples only goes on by extending the rankings included (so if the couples both had a majority with tied total ranks after first and second place votes, now they would compare those couples on 1st, 2nd, and 3rd place votes).
6. Keep going until all the couples are ranked.
A fuller description of the procedure and an example case can be viewed at http://www.swingdancecouncil.com/library/relativeplacement.htm. I'm looking at Parts C and D (Final Placements and Example).
(If there are any inconsistencies between my description above and the details on that page, assume that I screwed up. Sorry!)
Is there any way to do this in Excel?
Excel problems are always easier if you don't try to do everything all at once in one cell. In this case, for each couple, I would designate one cell to calculate the total number of 1st-places they received; one cell to calculate the total number of 1st-or-2nd-places; one cell to calculate the total number of 1st-or-2nd-or-3rd-places... Then compare all the 1st-place cells using rank() or similar, using a nested if to break ties by referring to the rank() among the 2nd-place cells, etc. Not elegant but gets the job done.
posted by foursentences at 11:17 AM on December 17, 2010 [1 favorite]
posted by foursentences at 11:17 AM on December 17, 2010 [1 favorite]
« Older Is there an car thief app for that? | What are some standard databases used for websites... Newer »
This thread is closed to new comments.
posted by cnanderson at 8:59 AM on December 17, 2010