Volunteered to add up the votes for my Book Club; No idea how to do it

January 18, 2016 10:14 AM

This year my Book Club decided to vote on the books we'll read all at once instead of month-by-month. There are 17 books choose from in total and I gave each person a sheet with the books in alpha order. I then asked them to order the books by preference – i.e., their first choice is #1, their second #2 etc.

Now I have 9 completed sheets and I am trying to figure out the best way to calculate the top 10 books of the 17 total. I think maybe Excel is the way to do this. But I have no idea how to set that up. I also have no idea if there is another good way to do this.

Question: How can I calculate the top 10 books without bursting into tears of frustration?

posted by Lescha to Grab Bag (6 answers total)

Does this web tool help? There are many ways to do this calculation (and the tool can do all of them).
posted by miyabo at 10:16 AM on January 18, 2016

Give each person-book points based on the 1-17 and then add those up per book. Highest score is the top pick.
posted by humboldt32 at 10:17 AM on January 18, 2016

There are tons of ways to do this, but maybe the simplest is adding up the overall ranking and pick the 10 lowest (so if everyone picked the same book as #1, it would have a score of 9). That would probably give you a basic idea of the consensus top ten. You could see if that gives you a clear result, in which case probably any vote counting system would give you about the same top 10.

You might have to break ties, which you could maybe do by, say, picking the one that received more #1 or #2 votes or whatever. Or flipping a coin. This isn't a presidential election, you probably should pick the lowest stress method for you.
posted by dismas at 10:17 AM on January 18, 2016

When I've done this I've made a spreadsheet, added up the votes (#1=1, #2=2, etc) for each thing, and divided by number of responses to get the average vote on each thing. The item with the score closest to 1 is the consensus #1 choice, then the next one is #2, and so on down the line.

There may be a more straightforward way involving math and formulas, but this is easy, doesn't require learning anything new if you just want it to get done now, and is easy to share/explain to others.
posted by phunniemee at 10:18 AM on January 18, 2016

This seems like a pretty easy Excel spreadsheet. Enter each book as a column, and each participant as a row. As you get their responses, enter the number that that participant entered for each book in the respective column. When you've got all your responses, sum up your columns, and the 10 lowest totals are your winners.

 |A|B|C|D|
-+-+-+-+-+
x|3|1|4|2|
-+-+-+-+-+
y|1|4|3|2|
-+-+-+-+-+
z|2|4|3|1|
-+-+-+-+-+

In this example, A sums to 6, B sums to 9, C to 10, and D to 5, you'd rank the books D-A-B-C. Make sense?
posted by kevinbelt at 10:20 AM on January 18, 2016

Any chance you could post your results for us?
posted by maxg94 at 8:00 AM on January 19, 2016