# Converting Binary to Decimal Using Dynamic Arrays in Excel

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))`

- Note that the input number
`1011`

in this example is saved in cell`A2`

.^{[return]}