Sorting out multiple rankings
April 8, 2018 4:03 PM Subscribe
Given multiple people's lists of ranking from 1 to X, how do I figure out the highest ranking items on the overall list?
I work for a very small community organisation that gives small grants. Each individual member want to look at the grant applications and rank their favourites from 1 to 27. It'll be my job to take that list and give them the top 10-15 (depending on how close the results are) as well as any contentious ones (those that receive wildly varying scores).
Due to various technical limitations in this organisation, I'm going to collate the rankings myself (possibly from people just emailing me their list). What's the best way for me to take those rankings and generate a list?
Possible complications:
1. My coding skills are kinda meh though I'm willing to learn
2. I don't know what to do about rankings where someone gave the same rank to multiple applications
Is there some kind of pre-existing solution for this? I know Excel/GDocs has a RANK function but I don't know how it works.
I work for a very small community organisation that gives small grants. Each individual member want to look at the grant applications and rank their favourites from 1 to 27. It'll be my job to take that list and give them the top 10-15 (depending on how close the results are) as well as any contentious ones (those that receive wildly varying scores).
Due to various technical limitations in this organisation, I'm going to collate the rankings myself (possibly from people just emailing me their list). What's the best way for me to take those rankings and generate a list?
Possible complications:
1. My coding skills are kinda meh though I'm willing to learn
2. I don't know what to do about rankings where someone gave the same rank to multiple applications
Is there some kind of pre-existing solution for this? I know Excel/GDocs has a RANK function but I don't know how it works.
Part of your problem is that there isn't a single, unambiguous way of doing this. There are a number of ranked voting schemes that you could choose from; what grouse described is equivalent to the Borda count method.
posted by teraflop at 5:14 PM on April 8, 2018 [6 favorites]
posted by teraflop at 5:14 PM on April 8, 2018 [6 favorites]
There is no absolute clean way to do it due to Arrow's Impossibility Theorem. Basically, ranked votes are a big mess to deal with. Instead of ranking, I'd recommend each member give each application a score from 1-10, and then take the average of the scores for each application.
posted by miyabo at 5:14 PM on April 8, 2018 [2 favorites]
posted by miyabo at 5:14 PM on April 8, 2018 [2 favorites]
My naive approach. Take the first persons ranking and give them 0..X in order (top place lost to zero people, second place lost to one person, etc.).
For each additional ranking, do the same but add the values to the numbers you already have for each candidate (ffirst place is zero so that candidate doesn't change, second place is one so that candidate has lost to an additional person).
In the end, each candidate has a 'number of people lost to' count, sort those and pick the lowest 10-15.
posted by zengargoyle at 5:23 PM on April 8, 2018
For each additional ranking, do the same but add the values to the numbers you already have for each candidate (ffirst place is zero so that candidate doesn't change, second place is one so that candidate has lost to an additional person).
In the end, each candidate has a 'number of people lost to' count, sort those and pick the lowest 10-15.
posted by zengargoyle at 5:23 PM on April 8, 2018
assuming spherical candidates
posted by zengargoyle at 5:34 PM on April 8, 2018 [4 favorites]
posted by zengargoyle at 5:34 PM on April 8, 2018 [4 favorites]
This is a well studied problem, since roughly the French Revolution- mathematical theory of ranked voting is the main topic. What you ask is considered an open problem.
If there are more than a few different ‘candidates’ - and it seems there are- a good first choice of methods is the Borda count, which has its roots in the fifteenth century.
posted by SaltySalticid at 5:42 PM on April 8, 2018 [1 favorite]
If there are more than a few different ‘candidates’ - and it seems there are- a good first choice of methods is the Borda count, which has its roots in the fifteenth century.
posted by SaltySalticid at 5:42 PM on April 8, 2018 [1 favorite]
Ask everyone to give each item on the list points from 1 - 27. Add up the total points for each item. Done.
(PS: I am 98% sure Survey Monkey can do this for you, for free.)
posted by DarlingBri at 5:42 PM on April 8, 2018 [2 favorites]
(PS: I am 98% sure Survey Monkey can do this for you, for free.)
posted by DarlingBri at 5:42 PM on April 8, 2018 [2 favorites]
I'd recommend each member give each application a score from 1-10, and then take the average of the scores for each application.
I do way more ranking of grant applications for funding agencies than I would like. Methods that are score-based rather than rank-based don't work well in this milieu. At best, this is because different raters will have very different ideas on what scores mean, unless you go through a score calibration exercise, and maybe even then. At worst, it's because individual raters will try to game the system.
The system I described is not unlike one the Canadian Institutes for Health Research used in recent years. It dealt with ties better.
posted by grouse at 5:50 PM on April 8, 2018 [5 favorites]
I do way more ranking of grant applications for funding agencies than I would like. Methods that are score-based rather than rank-based don't work well in this milieu. At best, this is because different raters will have very different ideas on what scores mean, unless you go through a score calibration exercise, and maybe even then. At worst, it's because individual raters will try to game the system.
The system I described is not unlike one the Canadian Institutes for Health Research used in recent years. It dealt with ties better.
posted by grouse at 5:50 PM on April 8, 2018 [5 favorites]
Hey, is my method (and DarlingBri's) a Borda count? Reading the Wikipedia link it seems the same except for a sign change.
I'm really curious because this was a 'Google Interview Question' except -- top three of 25 horses that can only run 5 at a time, how many races.
posted by zengargoyle at 6:27 PM on April 8, 2018
I'm really curious because this was a 'Google Interview Question' except -- top three of 25 horses that can only run 5 at a time, how many races.
posted by zengargoyle at 6:27 PM on April 8, 2018
How many people? I'd ask everyone to rank them 1-27 with No. 1 being their top choice and 27 being their last choice, then count up how how many points each grant. The one with the lowest points would be the highest ranked. If it's not a ton of votes, it shouldn't take too long to simply add them up.
You could create a Google Docs form for simplicity's safe so everyone can just vote there. I am not sure, but I think you could prevent them from voting with anything other than a number. I've responded to many Google Docs surveys but I've never created one myself.
posted by AppleTurnover at 8:10 PM on April 8, 2018 [2 favorites]
You could create a Google Docs form for simplicity's safe so everyone can just vote there. I am not sure, but I think you could prevent them from voting with anything other than a number. I've responded to many Google Docs surveys but I've never created one myself.
posted by AppleTurnover at 8:10 PM on April 8, 2018 [2 favorites]
The Associated Press (and other organizations) compiles rankings of the top 25 college football (and college basketball) teams.
Each voter turns in a ballot, with their top 25 teams (of 129 total) ranked in order. No ties allowed. The AP gives 25 points for each #1, 24 points for each #2, etc., and no points for any teams left off ballots. The published ranks are in descending order of total points. (They also publish "others receiving votes".)
If there are exactly 27 organizations, you can (as mentioned above) just add the ranks and take the lowest totals.
posted by Huffy Puffy at 5:09 AM on April 9, 2018
Each voter turns in a ballot, with their top 25 teams (of 129 total) ranked in order. No ties allowed. The AP gives 25 points for each #1, 24 points for each #2, etc., and no points for any teams left off ballots. The published ranks are in descending order of total points. (They also publish "others receiving votes".)
If there are exactly 27 organizations, you can (as mentioned above) just add the ranks and take the lowest totals.
posted by Huffy Puffy at 5:09 AM on April 9, 2018
If some of your issues is with people not following instructions and giving you bad lists, you will have more manual work ahead of you, but it can be sorted out. Consider idiot tie-rater who will rank grants A and F as #1, grants B, G, I as #2, grant J as #3,... all the way down to my lowest scoring grant C as #15, and I've not scored any of them as low as #27, biasing your scores too high. You'll have to correct it such that there's only one grant per line: A,F=average(1+2); B, G, I= average(3+4+5), J=6 ... C=27. I don't know of a good way to automate that. But then when it's done, you can average all of A's scores, which will include mostly integers and the non-integer 1.5 from the tie.
posted by aimedwander at 9:40 AM on April 9, 2018
posted by aimedwander at 9:40 AM on April 9, 2018
AppleTurnover's method is probably the best - instantly understandable to everybody. To take care of the "unusually contentious" ones, you can just calculate the standard deviation of the scores (stdevp or stdevpa will produce identical results in this case).
Contentious grants will have an unusually high standard deviation, which should be easy to spot just by eyeballing the results.
posted by zug at 11:46 AM on April 9, 2018
Contentious grants will have an unusually high standard deviation, which should be easy to spot just by eyeballing the results.
posted by zug at 11:46 AM on April 9, 2018
« Older What would be an awesome gift to give a military... | Where to stay and hike for a weekend in Taiwan? Newer »
This thread is closed to new comments.
PERCENTRANK.EXCfunction for this.AVERAGEof a set of quantiles as a combined score for everyone. Sort by this average and you can get the top 10–15.STDEV.Sof a set of quantiles to measure variability. Sort by this and you can get the most variable.posted by grouse at 4:18 PM on April 8, 2018 [3 favorites]