A to Z of Excel Functions: the BIN2DEC Function
19 December 2016
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 2N; in other words, it is the result of subtracting the number from 2N. 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 29 (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.