Excel Convert - Decimal Binary Hexadecimal Octal
Functions Used Converting Between Number Systems In Microsoft Excel
Jul 20, 2009
Microsoft Excel numbers in a spreadsheet are easily converted between the decimal, binary, hexadecimal and octal number systems. This is particularly useful to computer programmers. It is especially useful to those trying to control hardware using binary switches, or those trying to decipher a binary or hexadecimal file format.
Convert To Decimal From Binary, Hexadecimal and Octal
Conversion from binary, hexadecimal and octal to decimal is very straightforward. The three functions are:
BIN2DEC,
HEX2DEC, and
OCT2DEC
Examples: They are used in the following way.
=BIN2DEC(11111111) will give the answer 255
=OCT2DEC(7777) will give the answer 4095
=HEX2DEC(“ABCD”) will give the answer 43981
Convert To Binary, Hexadecimal and Octal From Decimal
The functions used are named similarly to those when converting to decimal, but an extra parameter is needed in the function. It is necessary to tell the function how many figures are needed in the answer. For binary, eight figures are used mostly. Octal typically uses three, and hexadecimal uses two.
The function names are:
DEC2BIN,
DEC2OCT, and
DEC2HEX
Example:
=DEC2BIN(255,8) will give the answer 11111111
=DEC2OCT(255,8) will give the answer 00000377
=DEC2HEX(255,8) will give the answer 000000FF
Binary, Octal and Hexadecimal Conversion Functions
Apart from the six functions described so far, there are six others:
BIN2OCT,
BIN2HEX,
OCT2BIN,
OCT2HEX,
HEX2BIN, and
HEX2OCT
These are used in exactly the same way as those described in the section “Convert To Binary, Hexadecimal and Octal From Decimal”.
Errors Found When Converting Between Number Systems In Excel
It is sometimes possible to enter the function and the cell shows #NUM! or #NAME? There are several possible causes of this.
1. An out of range digit is used. This means that a digit has been used in the argument that is not part of that number base. For example:
= OCT2DEC(8)
is not valid, because the digits in octal are 0 to 7, and 8 is not allowed. The same is true for BIN2DEC(2) or HEX2DEC(“G”).
2. Too many significant figures requested. For example,
= DEC2BIN(255,11) will give #NUM!, whereas DEC2BIN(255,10) will give 0011111111. Each function has a limit on the number of digits that may be returned.
3. Hexadecimal function arguments that use letters need to be inside quotation marks. For example,
=HEX2DEC(A) will result in #NAME? but
=HEX2DEC(”A”) will give 10.
4. There is a limit to the number range that may be converted. The actual range varies, depending on the number system being converted to, as well as the Excel version being used. For example, 255 decimal is the highest number allowed when converting to binary. Exceeding the maximum number allowed will give the #NUM! error. This can be worked around, however, by writing a function to extend the use. For instance:
=CONCATENATE(DEC2BIN(INT(A1/512),9),DEC2BIN(MOD(A1,512),9)) will extend the dec2bin function limit from 255 to 262,143
Potential Problems When Converting Between Number Systems in Excel
Negative numbers ignore the output format requested. For example,
=DEC2BIN(-1,8) will give the 10-digit result 1111111111. This may or may not be a problem for the user.
Another problem that the user needs to be careful of is that the output for all functions is a decimal number. In other words, the function
=DEC2BIN(15,8) gives the correct answer 00001111. However, the answer in the cell is treated by Excel as a decimal number, so the function
=SQRT( DEC2BIN(15,8) ) gives the answer 33.33 !
One final point is that the number system conversion function in Excel is set up for integers only, so the function
=DEC2BIN(15,8) will give the answer 00001111, whereas
=DEC2BIN(14.9999,8) will give the answer 00001110, or 14 in decimal.
Summary of Microsoft Excel Number System Conversion Functions
Microsoft Excel provides twelve functions that allow conversion between the decimal, binary, octal and hexadecimal number systems. They are very easy to use, although care must be taken when accessing them and using the results. More information about Excel functions is available free on-line, or in Excel for Dummies by Greg Harvey.
 |
