Advent of Code 2021 in Google Sheets: First 4 Days

Posted on by W.D.

You don’t need to be a “coder” to solve coding problems. If you know Microsoft Excel or Google Sheets, then you can solve these problems, too. There’s tons of overlap between coding and working in spreadsheets.

Sign up for the 2021 Advent of Code here. Take a stab at these problems with Microsoft Excel or Google Sheets.

Solutions in Google Sheets are provided below.

With my solutions, I am trying to minimize use of esoteric spreadsheet trickery (array formulas, OFFSET(), INDIRECT(), stuff like that).

I will also explain the answer in depth, which will not be interesting to many readers, but it should be helpful for a few.

Day 1

(columns start on row 2)

  • Column A:
    • [puzzle inputs]
  • Column B: (starting from row 2)
    • =iferror(int(A2 - A1 > 0), 0)
  • Solution:
    • =sum(B:B)

Very simple:

The above way of expressing it is my preferred way. But there are other equivalent approaches such as (ignoring the iferror() part):

  • if(A2 - A1 > 0, 1, 0)
  • if(sign(A2 - A1) = 1, 1, 0)
  • int(sign(A2 - A1) = 1)

Day 2

(columns start on row 2)

  • Column A:
    • [puzzle inputs]
  • Column B:
    • =split(A2, " ")
  • Column C:
    • [skip me]
  • Column D:
    • =if(B2 = "down", 1, if(B2 = "up", -1, 0)) * C2
  • Column E:
    • =if(B2 = "forward", C2, 0)

Start by splitting into two columns, delimiting by the space:

For depth, you don’t need to add and subtract. You can just add everything up, and multiply by “-1” for rows that you would have subtracted. For rows where you want to add, multiply by “1“:

The horizontal change is much simpler:

And then sum the two columns, then multiply, and we’re all set.

Day 3

(columns start on row 2)

  • Column A:
    • [puzzle inputs]
    • I prefer to not copy + paste as text, for ‘ol times sake. So let Google Sheets / Excel format it as a decimal number for you. Only weak-willed folks, such as software developers, should be intimidated by this.
  • Range P1:AA1:
    • [hardcode 0 through 11]
  • Columns B-M:
    • =rounddown( mod( $A2, pow(10, P$1+1) ) / pow(10, P$1) )
  • Range P2:AA2:
    • =mode(B:B)
  • Range P3:AA3:
    • =pow(2,P$1)*P2
  • Range P4:AA4:
    • =pow(2,P$1)*(1-P2)
  • Solution:
    • =sum(P3:AA3)*sum(P4:AA4)

This is what the whole thing looks like:

We are not intimidated by Google Sheets automatically formatting our binary fixed-width number into a decimal:

  • To isolate the ones digit, divide by 10 and check the remainder:
    • If it’s =1, then the ones digit is 1.
    • If it’s =0, then the ones digit is 0.
  • To isolate the tens digit, divide by 100, and check the remainder:
    • If it’s =11, then the tens digit is 1.
    • If it’s =10, then the tens digit is 1.
    • If it’s =1, then the tens digit is 0.
    • If it’s =0, then the tens digit is 0.

See the pattern? For each digit n from 0 to 11, we need to do the following steps to break down the number x:

  1. Calculate the remainder when dividing x by 10^(n+1): mod(x, 10^(n+1))
  2. Divide the number calculated above by 10^n.
  3. Round that number down.

Let’s take G3 as an example. The number x is 10100011100:

G3 is the 6th horizontal position, so n=5 (because our list goes from 0 to 11). We calculate the remainder when dividing by 10^6, then divide by 10^5.

All set? Now let’s calculate the most common digits. The most common digit is easy: =mode(B:B), then drag to the right. An alternate approach is =round(average(B:B), which works for binary numbers.

Now it’s time to calculate the “gamma” and “epsilon.” For gamma, take the individual digits and multiply each by 2^n, then take the sum.

For epsilon: do the same but instead of using the digit, use (1-digit). This operation flips the 1’s into 0’s, and 0’s into 1’s.

Then sum it all up, multiply, and there’s your answer:

Day 4

Day 4 is too complicated for me to walk through here. But rest assured, you can solve it with a spreadsheet, with zero manual parsing, zero VBA / Google App Scripts, and zero array formulas.

Here is a link to a solution.

Unfortunately we do need to do a little trickery with indirect() and/or offset() to compile the boards, and there are a few index() and match() operations.

The trick is to parse the input such that each row of the data represents a single possible “bingo”. Then, keep a running count of the numbers that get called per bingo. The row that has the leftmost column with a “5” represents the winner.

In a separate tab that contains the running tally, find the first board with a bingo solution that has a “5” in its running tally. Get that winner’s board number, print the board out, then it should be straightforward to do the remaining operations on it:

%d bloggers like this: