AOC Day 4 2021
This is part of a greater series about solving advent of code with spreadsheets.
Part One AND Part Two
This one is pretty funny. The author has managed to squeeze bingo into the story.
The input is both a list of numbers and boards. The goal is to figure out at which number call-out a board bingos. Part one asks for the board that bingos first. Part two asks for the one that bingos last.
Some notes …
- In this version of bingo, bingo comes from completing only a row or column. Diagonals don’t win.
- The center square isn’t free.
- The final output is of course a little bit of arithmetic. It is the sum of all uncalled numbers TIMES the last number called out.
Solving Part One and Part Two
This solution goes only halfway0. The spreadsheet is a “bingo calculator”. It takes a list of numbers and a single board and returns the winning score.
I copy-pasted each board in and wrote the round number and
score down1. I then MATCHed for the
lowest and highest number of moves. These could then be
INDEXed into scores.
(1) Very quickly how I automated that …
- The input was not “copied in”. It used dynamic lookup.
- A series of cells were set to equal to the calculator
results.
- To “freeze” a result, I copied and then pasted “values only”.
The bingo board has this representation. There will be many absolute references ($) to these cells.
| ROW 1 | 2 | 3 | 4 | 5 | |
|---|---|---|---|---|---|
| COL 1 | K2 | K3 | K4 | K5 | K6 |
| 2 | L2 | L3 | L4 | L5 | L6 |
| 3 | M2 | M3 | M4 | M5 | M6 |
| 4 | N2 | N3 | N4 | N5 | N6 |
| 5 | O2 | O3 | O4 | O5 | O6 |
Each row starts with a number call-out. Columns for each bingo square represent if they are or ever were called out2.
| CALLOUT | J8 | J9 | ... |
| DOT 1,1 | K8 = K$2=$J8 | K9 = OR(K8,K$2=$J9) | repeat → |
|---|---|---|---|
| 2,1 | L8 = L$2=$J8 | L9 = OR(L8,L$2=$J9) | repeat → |
| 3,1 | repeat ↓ | repeat ↓ | repeat → |
| 4,1 | repeat ↓ | repeat ↓ | repeat → |
| 5,1 | repeat ↓ | repeat ↓ | repeat → |
| 1,2 | P8 = K$3=$J8 | P9 = OR(P8,K$3=$J9) | repeat → |
| 2,2 | Q8 = L$3=$J8 | Q9 = OR(Q8,L$3=$J9) | repeat → |
| 3,2 | repeat ↓ | repeat ↓ | repeat → |
| 4,2 | repeat ↓ | repeat ↓ | repeat → |
| 5,2 | repeat ↓ | repeat ↓ | repeat → |
| etc | etc | etc | etc |
| 5,5 | AI8 = O$6=$J8 | AI9 = OR(AI8,O$6=$J9) | repeat → |
The score is calculated by conditionally summing across rows of the board. Win conditions are calculated with plain-old ORs and ANDs.
| SCORE | AJ8 = J8 * ( SUMIF(K8:O8, "=FALSE",K$2:O$2) + SUMIF(P8:T8, "=FALSE",K$3:O$3) + SUMIF(U8:Y8, "=FALSE",K$4:O$4) + SUMIF(Z8:AD8,"=FALSE",K$5:O$5) + SUMIF(AE8:AI8,"=FALSE",K$6:O$6)) |
repeat → |
|---|---|---|
| ROW WIN | AK8 = OR( AND(K8:O8), AND(P8:T8), AND(U8:Y8), AND(Z8:AD8), AND(AE8:AI8)) |
repeat → |
| COL WIN | AL8 = OR( AND(K8,P8,U8,Z8,AE8), AND(L8,Q8,V8,AA8,AF8), AND(M8,R8,W8,AB8,AG8), AND(N8,S8,X8,AC8,AH8), AND(O8,T8,Y8,AD8,AI8)) |
repeat → |
| WIN | AM8 = OR(AK8, AL8) | repeat → |
The exact row where the board first wins is calculated. The score on that row is fetched.
| WINNING ROW # | D2 = MATCH(TRUE, AM8:AM, 0) |
|---|---|
| WINNING SCORE | E2 = INDEX(AJ8:AJ, D2, 0) |
How Part One and Part Two Work
The Woes of Cross Referencing Lists
This challenge pushed the spreadsheet envelope. It captures one area of difficulty for me: cross-referencing lists3. To do something interesting on the product of two lists, one list must spread out over rows, and the other must spread out over columns. This can be unwieldy4.
(3) With the exception of what can be squeezed in a string
criteria in a SUMIF or COUNTIF
call.
(4) It is a pain to add a specific number of columns.
You may be interested in the previous or next day's solution.