AOC Day 6 2021
This is part of a greater series about solving advent of code with spreadsheets.
Part One AND Part Two
As always, read the problem text first. In summary, the goal is to model the growth of splitting fish. At 0 days away from splitting one fish becomes two. One of the new fish is 6 days away from splitting and the second is 8 days away.
The output to part one is the total number of fish on day 80. Part two is the total number of fish on day 256.
Solving Part One and Part Two
The key is counting the number of fish in each stage of splitting. Each day moves that count one stage further. This makes for a small spreadsheet0.
The comma separated list of initial fish goes into
A2.
| DAY/DATA | A2 | 1 | ... |
|---|---|---|---|
| STAGE 8 | B2 = COUNTIF( SPLIT($A2,","),"=8") |
B3 = J2 | repeat → |
| 7 | C2 = COUNTIF( SPLIT($A2,","),"=7") |
C3 = B2 | repeat → |
| 61 | D2 = COUNTIF( SPLIT($A2,","),"=6") |
D3 = C2 + J2 | repeat → |
| 5 | E2 = COUNTIF( SPLIT($A2,","),"=5") |
E3 = D2 | repeat → |
| etc | etc | etc | repeat → |
| 0 | J2 = COUNTIF( SPLIT($A2,","),"=0") |
J3 = I2 | repeat → |
| COUNT | K2 = SUM(B2:J2) | repeat → | repeat → |
(1) NOTE the formulas for stage 6 and 8.
- 8 needs to reference the number of fish splitting.
- 6 needs that number as well but also the fish at stage 7.
How Part One and Part Two Work
SPLIT into COUNTIF
The only clever formula in this parses input. While
COUNTIF works on a range, that range doesn’t have
to be in the spreadsheet. It can be the output of another
function like SPLIT.
This enables a one-liner for measuring the count of fish in some stage.
You may be interested in the previous or next day's solution.