A to Z of Excel Functions: The BIN2DEC Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the BIN2DEC function.

The BIN2DEC function

This function converts a binary number (base two) to a decimal number (base 10).

The BIN2DEC function employs the following syntax to operate:

The BIN2DEC function has the following arguments:

• number: this is required and represents the binary number you wish to convert to a decimal
• number cannot contain more than 10 characters (10 bits)
• the most significant bit of number is the sign bit
• the remaining nine bits are magnitude bits
• negative numbers are represented using two's-complement notation.

Two's complement is a mathematical operation on binary numbers, as well as a binary signed number representation based on this operation.  The two's complement of an N-bit number is defined as the complement with respect to 2^N; in other words, it is the result of subtracting the number from 2^N.  This is also equivalent to taking the ones' complement and then adding one, since the sum of a number and its ones' complement is all 1 bits.  The two's complement of a number behaves like the negative of the original number in most arithmetic, and positive and negative numbers can coexist in a natural way.

In English then, if number is 10 digits and the first number is 1, the number is deemed negative and 2⁹ (512) is subtracted from it, e.g. BIN2DEC(1111111111) = -1, being 511 (111111111 as a decimal) less 512.

It should be further noted that if number is not a valid binary number, or if number contains more than 10 characters (10 bits), BIN2DEC returns the #NUM! error value.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.