Converting Binary to Decimal Using Dynamic Arrays in Excel • Arnav Jain

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¹. 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))

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