Posted on

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.

## Summary

The steps being taken are:

Number Step 1 Step 2 Step 3 Step 3 Final
1011 1 1 3 8 11
2 0 2 0
3 1 1 2
4 1 0 1

With the final result after combining being:

``=SUM(POWER(2,LEN(A2)-SEQUENCE(LEN(A2)))*MID(A2,SEQUENCE(LEN(A2)),1))``

## Splitting up the text

We start with a binary number, in our case `1011`1. 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 `SEQUENCE` function.

Step 1 lists the starting position of each number. This is done using the following formula:

``=SEQUENCE(LEN(A2))``

The results of this operation are visualised in column Step 1:

Number Step 1
1011 1
2
3
4

Now we use the output from step one as an input to the `MID` function.

``=MID(A2,B2#,1)``

The results of this operation are visualised in column Step 2:

Number Step 1 Step 2
1011 1 1
2 0
3 1
4 1

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 `LEN` with `SEQUENCE`.

``=LEN(A2)-SEQUENCE(LEN(A2))``

The results of this operation are visualised in column Step 3:

Number Step 1 Step 2 Step 3
1011 1 1 3
2 0 2
3 1 1
4 1 0

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.

``=POWER(2,D2#)*C2#``

The results of this operation are visualised in column Step 4:

Number Step 1 Step 2 Step 3 Step 4
1011 1 1 3 8
2 0 2 0
3 1 1 2
4 1 0 1

## Finalising

We finalise this by summarising each digit that was the result from Step 4 above.

``=SUM(E2#)``

The results of this operation are visualised in column Final:

Number Step 1 Step 2 Step 3 Step 4 Final
1011 1 1 3 8 11
2 0 2 0
3 1 1 2
4 1 0 1

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:

``=SUM(POWER(2,LEN(A2)-SEQUENCE(LEN(A2)))*MID(A2,SEQUENCE(LEN(A2)),1))``

1. Note that the input number `1011` in this example is saved in cell `A2`. [return]