Running an F1 prediction league in a spreadsheet
By Danny de Visser, updated
An F1 prediction league runs fine in a spreadsheet. Each player needs four columns and one formula to score every driver. The work is not in the formula but in the upkeep: typing in the full result every race weekend, fixing it after penalties, and making sure everyone submits before the start.
What columns do you need?
An F1 prediction league spreadsheet needs four columns per player per session: the driver, where the player predicted him, where he actually finished, and the points. Use one tab per race with a block like that for each player, and a totals tab that adds everything up.
| Column | What goes in it | Example |
|---|---|---|
| A | The driver | Verstappen |
| B | Predicted position, 1 to 10 | 1 |
| C | Actual position, blank if not classified | 2 |
| D | Points, from the formula below | 3 |
Row 1 holds the headers and the ten drivers go in rows 2 to 11. Put the players' blocks side by side; copy the block for the next player and the formulas move along with it.
Which formula scores the points?
The formula that scores the points is =IF(C2="",0,MAX(0,5-2*ABS(B2-C2))), in D2 and filled down to D11. It gives 5 points for the exact spot, 2 fewer for every place off and never less than zero, the same points Predict the Race gives.
- Excel or Google Sheets in English
=IF(C2="",0,MAX(0,5-2*ABS(B2-C2)))- The session total, in D12
=SUM(D2:D11)
That is the same sum Predict the Race uses: 5 for exact, 3 for one place off, 1 for two places off, and nothing from three places off.
The IF part is there for blank cells. Without it, a blank counts as position 0, and a player can pick up points for a driver who had no classified position.
Where decimals are written with a comma, as in much of Europe, the commas in the formula become semicolons.
What about drivers outside the top 10?
Enter a driver outside the top 10 with his actual position, like any other. If a player puts someone in P10 and he finishes eleventh, that is one place off, so 3 points. Only leave the cell blank for a driver with no position in the official classification; he scores zero.
That is why you need the full result, not just the top 10. No position means, for example, not classified or disqualified.
Where does a spreadsheet let you down?
A spreadsheet league rarely goes wrong in the formula, and almost always in the upkeep. A few races in, one person is doing all the work: typing in every result, fixing it after penalties, chasing deadlines and sending round the standings, every single weekend.
- Typing in every result. The full qualifying and race result, every race weekend. Swap two rows and the standings are wrong, and nobody notices.
- Penalties after the flag. A time penalty after the finish reshuffles the result. Then you go back to that tab and check everything again.
- Deadlines. Who sent their top 10 before the start? In a group chat, "I had already typed it" is impossible to check, and a list that turns up after lights out starts an argument.
- Standings on a phone. You can share the sheet, but four columns per player is hard to read on a phone.
Can you do it without a spreadsheet?
Yes, an F1 prediction league works without a spreadsheet. Predict the Race scores the same way, but fetches the official result itself after every qualifying session and race. Predictions close when the session starts, so nobody can change anything afterwards.
If a penalty changes the result shortly after the race, the app scores it again. And the standings are on everyone's phone, showing who climbed and who dropped.
It is free and nobody needs an account. You share the league with a code in the group chat.
Frequently asked questions
Does this work in Google Sheets?
Yes, with the same columns and the same formula. If it returns an error, check the locale: where the decimal separator is a comma, the formula needs semicolons instead of commas.
How do I score the race winner and pole position?
With a row per question: right is points, wrong is zero. With the predicted winner in B14 and the actual winner in C14, =IF(B14=C14,25,0) gives the points. In Predict the Race the winner is worth 25 points and pole position 10, so you can borrow those numbers.
How do I put players on equal points in the same position?
Use RANK on the totals tab. With ten players in B2 to B11, =RANK(B2,B$2:B$11) gives players on the same points the same position: 1, 2, 2, 4. Predict the Race ranks the same way.
Read next
Ready to start?
Start your league in a minute and share the code with your friends.
Start your leagueNo group? Join the open league