Spreadsheet EPH

camestrosfelapton

February 10

I said that I made an Excel version of EPH awhile back. I couldn't find it again now, so I have made a Google Sheets version https://docs.google.com/spreadsheets/d/14gbLVIROrG-Ogmgex0cE6w8VVd9eYziu1cyufm-d4Go/edit?usp=sharing

Now, I haven't tested this so it may have bugs/errors or confused logic in it. I also haven't implemented the ✕60 so that it uses integer points instead of decimals (that wouldn't be hard to do but as this is more for demonstration than actual production use, I left it out).

The sheet is semi-manual. For a new set of ballots you'd need to copy formulas down extra rows if there are more voters and more works. You would also need to copy paste the columns that calculate each round in three sheets depending on the number of works nominated. Note that it is the NUMBER OF WORKS not the number of voters that determines how many rounds there are.

I *think* I've set it up to do ties correctly but I'd need to test that. If you spot errors, let me know. Like everything here, this is early draft, unchecked work.

How does it work

This is a spreadsheet that constantly keeps avoiding self-referenced formulas. It does this by having three separate sheets.

Ballots has the data as a list of voters and what they voted for. Each row is a combination of voter and a work. The sheet doesn't care how the data is sorted. Each round the sheet calculates the indvidual points for that specific voter and work, so long as the work is still in play.
Rounds is the main sheet. This sheet is closest in form to what you see in the nomination stats. It shows all the works, one row per work. Each round it tabulates values for those works still in play.
Elimination is where the works fight it out in the Thunderdome of EPH. It determines which works go into the next round.

Data about raw votes and points goes forward through the sheets Ballots>Rounds>Elimination and then eligibility* for the next round travels back down Elimination>Rounds>Ballots. Rinse and repeat until you are down to set of finalists.

*[not a great name in this context but here I mean "in play" not eligibility in the broad sense]

But how does it actually work?

There are a few spreadsheet tricks.

Match and Index: If your spreadsheets skills have advanced a bit further than just tabulating numbers, you probably have met VLOOKUP before. Match and Index are a similar idea but have a bit more flexibility. Match finds the row number where a specific set of data appears in another column. In this spreadsheet I can use Match to find where a work in Ballots appears in Rounds. I can then use Index to find data in other columns from the same row. Why bother? I wouldn't recommend resorting all the data but the idea is to make the sheet care less about how the raw data is sorted. If I've done it right, you should be able to resort columns without breaking things.
CountIf: A cyberpunk handle or a weird Q-Like Star Trek villain? CountIf counts things but only those things that meet some criteria. The sheet uses this to count only works that are still in play for a given round. The trick is that each round the work gets renamed, e.g. if Aeons is still in play but Zombie has been eliminated then Aeons shows as Y-Aeons and Zombie shows as N-Zombie. When the formula goes to count how many votes there are for Y-Zombie it comes back with 0.
SumIf: Just like CountIf but with a less cool name. SumIf adds stuff up in the same way CountIf counts stuff.
Rank: Ranks numbers. The trick is that works not in play get a blank cells when it comes to points so they don't get included in the ranking.

The elimination round ignores all the works except those ranked 1 or 2. I'm pretty sure that this gives me a way of dealing with ties for free. If two or more works are equal rank 1 then no work is ranked 2, so the ranks 1s have to fight it out. The elimination round checks which of the shown works has the smallest raw vote and flags it as eliminated. Then the eligibility/in-play status is updated for all works and then that ripples back to the other sheets.

But would it actually work?

I think so. For a busy category like Short Story with lots of works nominated, you'd need to copy and paste a lot more rounds and that would take a while. I don't know the limits of Google Sheets but modern Excel can handle quite large data sets. The raw ballots have to be in a particular format and obviously, the data needs to be clean with consistent names for all works. I've no idea what the data coming out of the nomination website would look like but I think the structure I used is the most obvious basic state you'd want (i.e. "long" format with one row being a voter-nominee combo rather than "wide" format with one row being a voter and five columns for their choices).

The poor Hugo admin would need to copy the last 10 rounds and tidy them up but to be honest it wouldn't be impossible to make a "last 10 rounds" sheet that had that set up already (I'd need to think it through though).

Should a Hugo Committee use Google Sheets to tabulate the nominations? No. That's an obviously bad idea. The formulas in each round are functionally the same in the spreadsheet but not literally the same, so there is way, way, way more scope for one column to have an error in it. A proper EPH program will just use exactly the same set of formulas every round, which makes checking and debugging a lot better.

What this spreadsheet version does do is SHOW every round dynamically. I think it also shows that EPH is relatively simple. It mainly is about tabulating some points in the same way every round but with one thing removed each round. There is not complex process of reallocating points or tracking who gets what. EPH only cares about the current moment.