1. Mathias Kools
2. Advent of Code 2021 in Spreadsheets
3. AOC Day 1 2021

AOC Day 1 2021

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

Part One

Read part one from the advent of code website.⁰ The input is a line-separated list of numbers. The goal is to count how many numbers are greater than the previous one.

Solving Part One

This challenge was easy with spreadsheets.¹

DATA	A2	A3	...
INCREASE	-	B3 = A3 > A2	repeat →
OUTPUT	C3 = COUNTIF(B3:B,"=TRUE")

I’ve omitted row numbers and columns and just done them inline. I think this format is more readable.

This solution scaled well to the challenge input of 2000 lines.

How Part One Works

The first spreadsheet of this series is worth explaining in full detail.

About the Colors

Generally, each column is like a “stage” of the program. Some stages rely on previous ones. To make these connections clear, columns and their formula references are colored².

Repeat

Formulas reference other cells by column (named with a letter A-Z), and by row (a number 1-2000). To copy formulas to other cells the user “drags”³ that cell over the row or column. When copied like this the cell formula updates to keep the same “relative” references.

For example …

• If a formula uses the cell to its left = A2 + 1
• The formula “dragged” to the next row will also use the cell to the left = A3 + 1
• The formula “dragged” to the next column still uses the cell to the left = B3 + 1

	1	2	3
A	DATA	199	399
B	HEADER	B2 = A2+1 => 200+1 => 201	B3 = A3+1 => 399+1 => 400
C	HEADER	C2 = B2+1 => 201+1 => 202

There is a way to instruct Google Sheets to keep a part of the cell reference absolute $. This isn’t used in this example.

Transposing Rows and Columns

Data is often pasted into thousands of rows. This isn’t as interesting as the formulas unique to each column. Additionally, with many columns the table can get quite wide. For these reasons I format tables with columns running along the top to bottom axis. Rows run from the left to right.

For example, a table looking like this in Google Sheets …

	A	B	C	...
1	HEADER1	HEADER2	HEADER3	...
2	A2	B2	C2	...
3	A3	B3	C3	...
...	...	...	...	...

Will look like this in this series …

	1	2	3	...
A	HEADER1	A2	A3	...
B	HEADER2	B2	B3	...
C	HEADER3	C2	C3	...
...	...	...	...	...

Boolean Values

Cells can have non-numeric data types⁴. My advent of code solutions rely mostly on numbers and booleans.

Operators like >, <, and =⁵ produce booleans. In the INCREASE column I use > to compare the current number to the previous number.

Formulas on Ranges

Cell ranges are continuous blocks of cells. To reference …

• The first four cells of column A, use A1:A4⁶.
• The range starting at cell A4 and going until the end of the column, use A4:A.

Some spreadsheet functions can act on cell ranges. COUNTIF counts the number of items in a range matching a “criterion”. Some example string criteria are …

• “=TRUE” for checking if a cell equals TRUE.
• “>=4” for checking if a cell is greater than or equal to 4.

Putting It All Together

• Paste the input into the first column (DATA).
• The second column computes if the current number is greater than the previous one A3 > A2.
• The third column uses a single cell counting the matches in the second column.

Part Two

Part two asks⁷ to count the number of 3-wide sliding windows with a sum greater than the previous one.

To understand 3-wide sliding windows think …

• Ruby Enumerable’s each_cons(3).
• Python itertool’s pairwise except for triples.
• Implemented as triplewise in more_itertools.

Solving Part Two

This requires one extra column.

DATA	A2	A3	A4	A5	...
3SUM8	-	-	B4 = A2+A3+A4	B5 = A3+A4+A5	repeat →
INCREASE	-	-	-	C5 = B5 > B4	repeat →
OUTPUT	D2 = COUNTIF(C5:C)

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