As part of the Advent of Code 2021 challenge, I decided to try and see if I could solve the daily challenges using Excel.
One step for the day 3 challenge includes converting binary numbers to decimal numbers. Excel has a built-in function for this,
BIN2DEC. However this function only supports a 10-bit binary number, but in practice only 9-bit numbers as the 1st bit is used for the polarity.
In a not so recent version of Excel, Microsoft introduced Dynamic Arrays. In this post I will show how to convert a binary number to a decimal number using dynamic arrays.
The steps being taken are:
|Number||Step 1||Step 2||Step 3||Step 3||Final|
With the final result after combining being:
Splitting up the text
We start with a binary number, in our case
10111. This number however is in one cell, to be able to perform operation on each digit, we need to split this up. This is done combining the
MID function and the new
Step 1 lists the starting position of each number. This is done using the following formula:
The results of this operation are visualised in column Step 1:
Now we use the output from step one as an input to the
The results of this operation are visualised in column Step 2:
|Number||Step 1||Step 2|
Now we have each digit in its own cell.
Calculating the exponent
Before we can perform the power operation, we need to know the base and the exponent. The base in our case is 2, but each digit will have it’s own unique exponent. Right rightmost digit has the exponent 0, the second rightmost digit has the exponent 1, so on and so forth.
This can be calculated using the following formula, combining the function
The results of this operation are visualised in column Step 3:
|Number||Step 1||Step 2||Step 3|
Now that we have the base and the exponent we can use the
power function. Note the multiplication in the formula below, this is to that the exponent for any digits with value 0 are discarded.
The results of this operation are visualised in column Step 4:
|Number||Step 1||Step 2||Step 3||Step 4|
We finalise this by summarising each digit that was the result from Step 4 above.
The results of this operation are visualised in column Final:
|Number||Step 1||Step 2||Step 3||Step 4||Final|
Most times we want to perform all these steps in a single step, we can therefore combine all of the steps to a single step using the following formula:
- Note that the input number
1011in this example is saved in cell