Excel Pluribus Hugo

[Note: I’m very much not an expert on this proposal – this was the easiest way for me to make sense of it at a practical level. I may have well misunderstood aspects of the process]

Yay! I think I have finally removed all the kinks from my Excel version of the proposed new nomination system for the Hugo awards known as “E Pluribus Hugo” or EPH (and of course look here and interesting comments here).

The proposal basically boils down to people nominating what they like in a simple manner i.e. submit five (or fewer) things you want nominated in a category but then adding a more complex way of tallying the votes. Each work you nominated is weighted by the number of things you nominated – so if you nominate 5 things each one is getting 1/5 (0.2) of a vote from you, nominate four things and each thing gets 0.25 of a vote from you.

Nominated works then go through an elimination process. First they would find the two nominees with the lowest weighted score, then they would compare the total number of raw nominations (not weighted) each of those two works got. The one with the fewest raw nominations is eliminated. The clever bit is that with that work gone, anybody who nominated it has now got a slightly more strongly weighted vote for everything else they nominated.

In theory it would mean a slate or block vote would improve their success of getting one work on the final ballot but drastically reduce the chance of them getting multiple works on the ballot.

Now I wanted to model it in a transparent way – hence a spreadsheet rather than a program. With an Excel spreadsheet you see a static snapshot of all the stages in one go. I didn’t want to us any VBA or Pivot Tables either and I wanted to set up a complete round so that I could then copy and paste one round after another to make a complete process without editing formulas as I went.Unfortunately doing that means I’ve had to use some quirky lookups and the Indirect function as well as the funky Sumif. I haven’t done a thorough check of everything yet but it has completed one toy nomination process of 20 works (down to 5 nominees).

For this first run I used a flatish distribution of 2000 votes picking 5 nominees from 20 possible nominees. This was done by picking a rnadom number for each nomination from 1 to 20 (RANDBETWEEN). In some cases this could result in a single ballot containing the same work twice but I ignored that issue in this trial run as it wasn’t intended to be realistic data.

The made-up works with weights and raw counts were (if Numbers hasn’t messed up the Excel sheet.

Weights

Nominees Count

22

Age of the Legacy

110

19.4

Dawn of the Venus

97

19.8

Dialectic of the Quanto-Puppies

99

19.6

Existance  Puppies

98

19.8

Genesis  Mars

99

20.4

Grondleish before the Memorandum

102

23

Horror  Age

115

20.8

Horror  Elf

104

18.8

Legacy  War

94

23.6

Legend  Age

118

18.8

Minmax between the Meta-Age

95

20.4

Neptune under the Meta-Venus

103

20.4

Neuron  Entities

103

18.6

Neuron  Gun

94

19.2

Nuclear  Weft

96

19.8

Proxima  Entities

101

19.4

Quark  Punk

98

17.4

Rise  Punk!

87

19.8

Steam  Process

99

17.4

Synthesis  Cyber-Tyrrany

88

And the voting was completed in 13 rounds (a tad quicker than 15 rounds because of a couple of ties for bottom)

Round 1 Rise  Punk!
Round 2 Synthesis  Cyber-Tyrrany
Round 3 Minmax between the Meta-Age
Round 4 Legacy  War
Round 5 Neuron  Gun
Round 6 Nuclear  Weft
Round 7 Genesis  Mars
Round 8 Proxima  Entities
Round 9 Neptune under the Meta-Venus
Round 10 Grondleish before the Memorandum
Round 11 Steam  Process Dialectic of the Quanto-Puppies
Round 12 Existance  Puppies Quark  Punk
Round 13 Neuron  Entities

Next step is to try and make a Google Docs version. 🙂

Advertisements