A to Z of Excel Functions: The BIN2HEX Function

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

The BIN2HEX function

We don’t want to put the hex on this function, but this converts a binary number (base two) to a hexadecimal number (base 16).

The BIN2HEX function employs the following syntax to operate:

The BIN2HEX function has the following arguments:

• number: this is required and represents the binary number you wish to convert.  It should be noted that number cannot contain more than 10 characters (10 bits) and that 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
• places: this is optional and represents the number of characters to use.  If places is omitted, BIN2HEX uses the minimum number of characters necessary.  The argument places is useful for padding the return value with leading 0s (zeros).

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.

It should be further noted that:

• If number is not a valid binary number, or if number contains more than 10 characters (10 bits), BIN2HEX returns the #NUM! error value
• If number is negative, BIN2HEX ignores places and returns a 10-character hexadecimal number
• If BIN2HEX requires more than places characters, it returns the #NUM! error value
• If places is not an integer, it is truncated
• If places is nonnumeric, BIN2HEX returns the #VALUE! error value
• If places is negative, BIN2HEX 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.