At the New York office, we decided to make it a bit more exciting by starting up the ole office pool. 8 people each got four random teams. The team owner of the World Cup winner wins the pot of “Friendship Points.” I drew Costa Rica, Panama, Senegal, and Germany.
I expected neither Costa Rica nor Panama to advance to the knock out rounds, their groups are too difficult. Senegal had a reasonable chance of making it to the round of 16, maybe. Despite losing to Mexico on their first game, Germany seemed a very lucky draw for me and I thought they had a good chance to go all the way.
By the end of the second day, we had a Google Sheet tracking games. (I’ve changed the names to protect the “innocent”)
It was semi-informative and it was very colorful — I would have NONE of it!
The problem in most spreadsheets, even with such a small amount of data, is that the answers to questions are very hidden. There was one thing I really needed to know — “Am I Winning?”
So, I redesigned the sheet slightly. Visually I broke up groupings with grey and white backgrounds.
I made it so the only thing that needed to be edited was the score — given a little more time I would have spidered some site for it.
Hidden within columns K and O are the number of points each team gained for that game — 3 points for a win, 1 point for a tie, 0 points for a loss. The Google Sheets language is passible at best — this statement is written as ‘=IF(J2 = “”, “”, IF(J2 > N2, 3, IF (J2 = N2, 1, 0)))’. Not horrible, yet.
Hidden within Columns B, C, D are internal IDs for the game ie Portugal vs. Spain would be called both “A1Portugal” and “A1Spain“.
The bolding and greying for winning teams happened with “Conditional Formatting…” checking the points columns (K and O).
Now I can quickly quickly tell what teams have won, and to whom they belong. But, “Am I winning?”
The quality of winning, in the group stage, to advance to the knock out rounds. Each team plays three games and awarded points for each game, the two teams with the most points will advance to the knock out rounds. Tie breaks are determined by Goal Differential (The number of goals the team scores minus the number of goals the team gives away), Total Goals Scored, Head to Head results, some stuff that doesn’t normally happen for a three way tie and finally a coin toss.
The goal is to have this sheet entirely data driven.
The Group Points are derived from the previous sheet. I cheat by hiding a gameid Column D and using that to look up the points for the game in Column E. It is simple but the formulas start looking worse “=IF(ISERROR(VLOOKUP(D3, ‘Scores’!$C$2:$Z$99, 9, FALSE)), VLOOKUP(D3, ‘Scores’!$D$2:$Z$99, 12, FALSE), VLOOKUP(D3, ‘Scores’!$C$2:$Z$99, 9, FALSE))“. The same happens for the 2nd and 3rd games.
Goal Diff. and Total Goals also come from that ‘Scores’ Sheet. My background is more SQL than Excel. So when in doubt, write SQL-like queries over Excel formulas. Total Goals looks something like . “=QUERY(‘Box Scores’!$A$1:$Z$99, “SELECT SUM(J) WHERE H = ‘” & B3 &“‘ label sum(J) ””, 0) + QUERY(‘Box Scores’!$A$1:$Z$99, “SELECT SUM(N) WHERE L = ‘” & B3 &“‘ label sum(N) ””, 0)“. It is not pretty, but it works.
Instead of writing tons of formula logic to determine tie breaks, I did a math cheat. In a hidden column I called “Tie Break Sausage Maker.” “=J3*1000000 + Q3*10000 + R3*100 + RANDBETWEEN(0,99)” yields a sortable score. That score is used to generate a rank for each Group. (I do NOT account for Head to Head tie breaks. )
“Advance As” is the part of the sheet I haven’t automated yet. I would have liked it to determine given the points a team has and the upcoming schedule has the team clinched a specific spot in the knockout rounds, any spot in the knock out rounds or has been eliminated. (That might get implemented by the end of the week, or 2026 when the North America hosts the World Cup).
I snuck in a boolean in columns M, N, and O to tell whether the team is advancing, potentially advancing or has been eliminated
“Conditional Formatting…” uses those columns to bold or gray out teams.
Now I can quickly quickly tell what teams have the possibilities of advancing, and to whom they belong. But “Am I Winning?”
This gives me the answer, an aggregate of owners to teams to see how I match up.
I’m Gary. The answer is “No.”
But beauty of Google Sheets is if just any of my, currently, one team that will make it to the knockout rounds, I will have a brand new shared relatively easy to implement spreadsheet to ask again — “Am I Winning?”
A copy of the Google Sheet viewable at https://docs.google.com/spreadsheets/d/1YR6Coj_uG4mBR_QWS-6VMvtDUs2AHdzmL_6_LcKafbY/edit?usp=sharing