AOC Day 2 2021

This is part of a greater series about solving advent of code with spreadsheets.

Part One

As always, read the problem text first. In summary, the input is a list of movement commands. The format of the data is <direction> <amount> where direction can be forward, down, and up⁰.

(0) NOTE there is no backwards. There are sometimes unsaid invariants. <amount> is always a positive integer in my challenge input.

Starting at (0,0) the objective is to figure out the final position.

forward increases x.
down increases y.
up decreases y.

Finally multiply x by y¹.

(1) The advent of code challenges will often ask for a single number instead of two.

Solving Part One

This can be squished into a single table.

DATA	-	A3	...
MOVE	-	B3,C3 = SPLIT(A3, " ")	repeat →
AMOUNT²	-	↑ SPILLOVER ↑	repeat →
X	0	D3 = D2 + IF(B3="forward",C3,0)	repeat →
Y	0	E3 = E2 + IF(B3="up",-C3,0) + IF(B3="down",C3,0)	repeat →
OUTPUT	0	F3 = D3 * E2	repeat →

(2) I had to coerce the type of AMOUNT in Google Sheets. I formatted the column as a number.

How Part One Works

SPLIT

According to the rules, getting data into the right format isn’t required. I’ve done it anyway using the SPLIT formula. This function splits text into multiple values with a separator. When a function returns multiple values it “spills over”³ in neighboring cells.

(3) Spilling is my word. I don’t know the official term for it.

DATA	A2 = hi,my	A3 = name,is,slim shady
SPLIT	B1 = SPLIT(A1,",") => hi	B2 = SPLIT(A2,",") => name
SPILL	my	is
MORE SPILL		slim shady

IF Statements

IF^{4} is a function returning the first value if the boolean is TRUE, and the second if it is FALSE.

(4) If you’re curious, there is an elseif substitute: IFS.

Part Two

Part two re-interprets the moves up and down. No longer do they change the y coordinate, instead they change the aim.

down increases aim.
up decreases aim.
forward BOTH increases x by the amount BUT ALSO increases y by aim * amount.

Solving Part Two

Solving part two requires one extra column.

DATA	-	A3	...
MOVE	-	B3 = SPLIT(A3, " ")	repeat →
UNITS	-	↑ SPILLOVER ↑	repeat →
AIM	0	D3 = D2 + IF(B3="down",C3,0) + IF(B3="up",-C3,0)	repeat →
X	0	E3 = E2 + IF(B3="forward",C3,0)	repeat →
Y	0	F3 = F2 + IF(B3="forward",C3*D3,0)	repeat →
OUTPUT	0	G3 = E3*F3	repeat →

You may be interested in the previous or next day's solution.