approaching a coding problem using Excel



Every year in December, Advent of Code releases an advent calendar of small programming puzzles to participating users that can hypothetically be solved using any programming language. Although I didn't finish the entirety of the calendar, I made a meaningful attempt at a few of the problems using Python. These attempts can be reviewed in this repo. I was able to transform a Python solution to one of the puzzles into an Excel approach, which is summarized in this post.

$ the problem


The full explanation of the problem can be reviewed here. In summary, I am supposed to pilot a hypothetical submarine to retrieve sleigh keys that flew into an ocean (read the day 1 release for more context of the problem). I am given a list of commands to pilot the submarine based on horizontal position and depth readings. To get the correct answer for the puzzle, the final horizontal position and depth of the submarine must be multiplied. A sample list of commands are provided as follows: forward 5, down 5, forward 8, up 3, down 8, forward 2. "Forward" readings add to the horizontal position, while "up" and "down" readings decrease and increase, respectively, the depth. Based on this sample list of commands, the product of the final horizontal position and depth readings is 150.

$ the algorithm


Solving this problem using Python is relatively straightforward. Generally, the list of commands, which can be any unspecified length, can be pasted into a text file and read by the built-in open() and readlines() functions. Then the following algorithm is performed (my exact Python code can be found here):

  1. Read line
  2. Parse the line by a single space delimiter
  3. If the first parsed token is a "forward" instrution, add the value in the second parsed token to the horizonal position
  4. If the first parsed token is a "down" instrution, add the value in the second parsed token to the depth
  5. If the first parsed token is an "up" instrution, subtract the value in the second parsed token from the depth
  6. After all lines of the file has been read, multiply the final values of the horizontal position and the depth to retrieve the answer

Approaching the problem using Excel will require a different approach due to the fact that Excel is a tool, not a programming language, and using its native functions are constrained by tabular formatting (i.e. the spreadsheet). Here is my general approach: the list of commands, which can still be of any unspecified length, must be pasted into the first ("A") column. The second ("B") column is reserved for horizontal position values, the third ("C") column is reserved for depth values, and the fourth ("D") column is reserved for the products of horizontal position and depth values ("B" and "C" columns). I added labels for each column in the first row, and in the second row, I initialized the B, C, and D columns with 0 values.

With this tabular set up, the following is then performed on each row starting on the third row:

  1. In column B, parse the value in column A (i.e. the specific command) by a single space delimiter, and if the first parsed token is a "forward" instruction, add the value in the second parsed token to the horizonal position in the previous row
  2. In column C, parse the value in column A (i.e. the specific command) by a single space delimiter, and if the first parsed token is a "down" instruction, add the value in the second parsed token to the depth in the previous row, but if the first parsed token is an "up" instruction, subtrat the value in the second parsed token from the depth in the previous row
  3. In column D, multiply the values in columns B and C

Then using native Excel features, apply this same logic throughout rows in a given column (i.e. drag the fill handle).

$ the implementation


In order to implement this solution, having knowledge of Excel functions is required. Below are the functions and other Excel components I used to build the logical implementation along with their short descriptions:
  • =IF(condition, statement if condition is evaluated true, statement if condition is evaluated false)
  • =LEFT(string/word to parse, index starting from the left of the string/word) - returns the calculated substring/subword
  • =RIGHT(string/word to parse, index starting from the right of the string/word) - returns the calculated substring/subword
  • =FIND(substring/subword, string/word) - returns the index location of the substring/subword in the string/word
  • =LEN(string/word) - returns the number of characters/length of the string/word
  • =PRODUCT(first value, second value) - returns the product of the two values
  • @INDIRECT - gets the referenced location (instead of direct value) of a specified cell range

Putting all these components together, we can construct the formulas required to make calculations on the horizontal position and depth readings. For the first calculations on the third row, prior to dragging the fill handle on the rest of the rows, cell B3, C3, and D3 can be filled with the following formulas, respectively:
=IF(LEFT($A3, FIND( " ",$A3) - 1 )= "forward",@INDIRECT("B" & ROW() -1) + RIGHT(
$A3, FIND(" ",$A3)-LEN(LEFT ($A3, FIND(" ",$A3) - 1 ))),$B2)=IF(LEFT($A3, FIND( " "$A3) - 1 ) = "down",@INDIRECT("C" & ROW() -1) + RIGHT(
$A3, FIND(" ",$A3)-LEN(LEFT ($A3, FIND( " ",$A3) - 1 ))),IF(LEFT ($A3, FIND(" ",$A3)
-1)= "up",@INDIRECT("C" & ROW() -1) - RIGHT($A3, FIND(" ",$A3)-LEN(LEFT($A3,
FIND(" ", $A3) - 1 ))),$C2))=PRODUCT($B3,$C3)

After applying this logic through the rest of the rows via dragging the fill handle, the final value (the last row) of column D should contain the answer to the puzzle.  


Written: March 26, 2022


GITHUB / EMAIL