Conversion functions fall into the following categories: number systems, the binary system, one’s complement, and two’s complement.
This category contains a series of conversion functions that convert numbers from one number system to another.
The most frequently used and most familiar number system is the decimal system, which works with ten distinct symbols and is derived from our ten fingers. In the English language, this connection can still be found: digit can mean finger as well as number.
With the development of data processing, a need arose for a number system that would accommodate the technical factors of the former calculating machines. The general opinion is that the binary system was introduced because calculating machines were binary calculators, and they were able to work with only two states, on and off. (By the way, in 1943, Konrad Zuse built the first programmable binary calculator, the Z3.)
Note
What you just read is only half the truth about the introduction of the binary system into calculation technology: Just as important, or even more so, is the fact that the binary system has much easier calculation rules.
It’s hard enough for our abecedarians to learn all the various formulas of our beloved decimal system in math class—3+4, 3+5, 4+7, and so on—and in addition to that, the multiplication tables! To build all that into the mechanical calculation machines was completely impossible back then.
The binary system can basically get by with three calculation rules on which all the others are based:
0 + 0 = 0
0 + 1 = 1 or 1 + 0 = 1
1 + 1 = 10
In addition to the familiar decimal system, there are three other systems that are used in calculation technology. Because Excel can use these four number systems, twelve conversion functions are needed: four number systems can each be converted into the three others.
The number systems are:
Decimal (10)
Binary (2)
Octal (8)
Hexadecimal (16)
The number in parentheses is the base for each number system and thus the number of the digits this number system has. Because the hexadecimal system requires more digits than we have available, the missing digits are represented by the letters A through F. (Not to be confused with the hexagesimal system (actually, the sexagesimal system) with a base of 60, which is used to measure angles, geographic lengths and widths, and time. It is the foundation for old numbering terms such as dozen or great gross as well.) Table 17-5 lists the number systems.
Because the binary system allows only two values per number position, binary numbers can quickly become very long and are difficult to read. Therefore, either of the following can be done:
Three binary digits are combined into one base-8 number system (octal system).
Four binary digits are combined into one base-16 number system (hexadecimal system).
2011 (decimal) = 11111011011 (binary) = 3733 (octal) = 7DB (hexadecimal)
It is obvious that the number system with the largest base (hexadecimal here) is using the fewest digits.
In general, the relationship between the value range M, which can be expressed with a number of a certain length in a base-B number system, and the number of digits n is
M = Bn
For example, eight digits (n = 8) in the binary system (B = 2) can express M = 28 = 256 numbers as its maximum. The value range is 0 through 255. Because only positive numbers can be represented this way, these numbers are called unsigned integers. (This corresponds to the data type Byte in Microsoft Visual Basic for Applications [VBA].)
We are familiar with negative numbers with a minus sign in front of them. For a computer with its binary system (based on the two symbols 0 and 1), there is no space for a third symbol, which is the leading sign. Therefore, the leftmost binary number—the most significant bit (MSB)—is reserved to represent negative numbers. Negative numbers have a 1 in this position, and positive numbers have a 0.
For the computer to be able to calculate with these numbers, the direction of the negative numbers must be inverted. For binary numbers, the following applies as well: the larger the absolute value, the smaller the negative number. This is achieved by inverting the individual digits (with the exception of the sign bit). Each 1 becomes a 0, and vice versa. The result is the one’s complement. This has several disadvantages that will not be explored here, except for the fact that the one’s complement has two zero values: +0 and –0, which are mathematically but not technically the same.
To perfect this number representation, a 1 is added, and a possible overflow is dropped. This results in the two’s complement. Its value range is –128 to +127. The computer can uniformly apply the calculation rules of the unsigned numbers. The numbers are simply interpreted differently. This basically applies to larger numbers as well, just the number of digits increases. (This is how the data types Integer and Long are represented in VBA.) The binary numbers in Excel also follow this number representation. They can have no more than 10 bits and therefore cover the value range of –512 to +511 (because 210 = 1024).
Definition. This function converts a binary number into a decimal number.
Argument
Number (required) The (at most) 10-digit binary number in two’s complement notation (see the earlier discussion) that is to be converted into a decimal number
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Examples. The following examples illustrate the BIN2DEC() function.
=BIN2DEC(1110)returns14.=BIN2DEC(111111111)returns511.=BIN2DEC(1111111111)returns–1.=BIN2DEC(1111111110)returns–2.
The worksheet in Figure 17-1 contains many additional examples, including the conversion into the octal and hexadecimal systems.
Syntax. BIN2HEX(Number,Places)
Definition. This function converts a binary number into a hexadecimal number.
Arguments
Number (required) The at most 10-digit binary number in two’s complement notation (see the section titled Two’s Complement) that is to be converted into a hexadecimal number. If Number has a negative value, a 10-digit hexadecimal number is returned.
Places (optional) Determines how many digits of the hexadecimal number are displayed and is used to display leading zeros in the result. If the argument Places is omitted, only the required number of digits is displayed. Possible decimal places after the decimal point are ignored.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Caution
The result of BIN2HEX() is a string that cannot readily be used for additional calculations. If necessary, you can use text functions for the returned hexadecimal numbers.
You should pay special attention to the fact that formulas that link references to the hexadecimal numbers with arithmetic operators do not generate error messages, as long as those numbers only contain digits from the decimal system (0 to 9). They are interpreted as decimal numbers, and the formula returns a result that is not intended.
The following example shows such a case:
=BIN2HEX(111100100) returns 1E4 as a hexadecimal number. If this value is used via a cell reference in a formula, it could be interpreted as “1.00E+04”; that is, as a completely different number in scientific format. Because no error message is displayed in the table, such an error is hard to find.
Examples. The following examples illustrate this function.
=BIN2HEX(1110)returns the hexadecimal numberE.=BIN2HEX(1110,4)returns the hexadecimal number000E.=BIN2HEX(111111111)returns1FF.=BIN2HEX(1111111111)returnsFFFFFFFFFF(corresponds to -1decimal).=BIN2HEX(1111111111,4)also returnsFFFFFFFFFF(corresponds to -1decimal).
You can find additional examples in the »examples for BIN2DEC().
Syntax. BIN2OCT(Number,Places)
Definition. This function converts a binary number into an octal number.
Arguments
Number (required) The (at most) 10-digit binary number in two’s complement notation (see the section titled Two’s Complement) that is to be converted into an octal number. If Number has a negative value, a 10-digit octal number is returned.
Places (optional) Determines how many digits of the octal number are displayed, and is used to display leading zeros in the result. If the argument Places is omitted, only the required number of digits is displayed. Possible decimal places after the decimal point are ignored.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Caution
The octal numbers returned by this function are strings that cannot readily be used like common numbers in other formulas. If an octal number is used in a formula with an arithmetic operator, that number is interpreted as a decimal number. For example, if the cells C13 and C14 contain the octal numbers 320 and 377, the formula
=C13+C14
returns the value 697, which is false. It already becomes obvious from the number 697, which is not a valid octal number, because the digit range of the octal system is only 0 through 7, and the digit 9 is not part of it. The octal number 717 would be correct.
The following formula works correctly:
=DEC2OCT(OCT2DEC(C13)+OCT2DEC(C14))
Examples. The following examples illustrate this function.
=BIN2OCT(1110)returns16.=BIN2OCT(1110,4)returns0016.=BIN2OCT(111111111)returns777.=BIN2OCT(1111111111)returns7777777777(corresponds to –1decimal).
You can find additional examples in the examples for BIN2DEC().
Syntax. DEC2BIN(Number,Places)
Definition. This function converts a decimal number into a binary number.
Arguments
Number (required) The decimal number that is to be converted into a binary number in two’s complement notation (see the section titled Two’s Complement). The valid entry values for Number are from –512 through +511. If the argument Number is not an integer, the decimal places are ignored. If Number has a negative value, a 10-digit binary number is returned.
Places (optional) Determines how many digits of the binary number are displayed, and is used to display leading zeros in the result. If the argument Places is omitted, only the required number of digits is displayed. Possible decimal places after the decimal point are ignored.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Caution
Excel functions regard the obtained binary numbers as text. Therefore, these numbers cannot be used for calculations with other formulas without special provisions. Only text functions can be used to process them further.
If formulas with arithmetic operators process references to these binary numbers, Excel does not generate error messages but rather interprets them as decimal numbers, which of course leads to false results.
Examples. The following examples illustrate how to use DEC2BIN().
=DEC2BIN(14)returns1110.=DEC2BIN(341)returns101010101.=DEC2BIN(6,8)returns00000110.=DEC2BIN(-1)returns1111111111.=DEC2BIN(-1,8)also returns1111111111.
The worksheet in Figure 17-2 contains many additional examples, including the conversion into the octal and hexadecimal systems.
Syntax. DEC2HEX(Number,Places)
Definition. This function converts a decimal number into a hexadecimal number.
Arguments
Number (required) The decimal number that is to be converted into a hexadecimal number in two’s complement notation (see the section titled Two’s Complement earlier in this chapter). The valid entry values for Number are –549,755,813,888 through +549,755,813,887. If the argument Number is not an integer, the decimal places behind the decimal point are ignored. If Number has a negative value, a 10-digit hexadecimal number is returned.
Places (optional) Determines how many digits of the hexadecimal number are displayed, and is used to display leading zeros in the result. If the argument Places is omitted, only the required number of digits is displayed. Possible existing decimal places after the decimal point are ignored.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Caution
The result of DEC2HEX() is a string that cannot readily be used for additional calculations. If necessary, you can use text functions for the returned hexadecimal numbers.
You should pay special attention to the fact that formulas that link references to the hexadecimal numbers with arithmetic operators do not generate error messages, as long as those numbers only contain digits from the decimal system (0 through 9). In this case, they are interpreted as decimal numbers, and the formula returns an unintended result.
The following example illustrates such a case:
=DEC2HEX(123456) returns 1E240 as a hexadecimal number. If this value is used through a cell reference in a formula, it can be interpreted as 1E+240, that is, as a completely different number in scientific format.
Examples. The following examples illustrate the function.
=DEC2HEX(14)returnsE.=DEC2HEX(31,4)returns001F.=DEC2HEX(341,4)returns0155.=DEC2HEX(-5)returnsFFFFFFFFFB.=DEC2HEX(-5,4)also returnsFFFFFFFFFB.
You can find additional examples in the examples for DEC2BIN() in DEC2BIN().
Syntax. DEC2OCT(Number,Places)
Definition. This function converts a decimal number into an octal number.
Arguments
Number (required) The decimal number that is to be converted into an octal number in two’s complement notation (see the section titled Two’s Complement earlier in this chapter). The valid entry values for Number are from –536,870,912 through +536,870,911. If the argument Number is not an integer, the decimal places are ignored. If Number has a negative value, a 10-digit octal number is returned.
Places (optional) Determines how many digits of the octal number are displayed, and is used to display leading zeros in the result. If the argument Places is omitted, only the required number of digits is displayed. Possible decimal places after the decimal point are ignored.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Caution
The octal numbers returned by this function are strings that cannot readily be used like common numbers in other formulas. If an octal number is used in a formula with an arithmetic operator, that number is interpreted as a decimal number. For example, if the cells C13 and C14 contain the octal numbers 320 and 377, the formula
=C13+C14
returns the value 697, which is false. It already becomes obvious from the number 697, which is not a valid octal number, because the digit range of the octal system is only 0 through 7, and the digit 9 is not part of it. The octal number 717 would be correct.
The following formula works correctly:
=DEC2OCT(OCT2DEC(C13)+OCT2DEC(C14))
Examples. The following examples illustrate this function.
=DEC2OCT(14)returns16.=DEC2OCT(31,4)returns0037.=DEC2OCT(341,4)returns0525.=DEC2OCT(-5)returns7777777773.=DEC2OCT(-5,4)also returns7777777773.
You can find additional examples in the examples for DEC2BIN() in DEC2BIN().
Syntax. HEX2BIN(Number,Places)
Definition. This function converts a hexadecimal number into a binary number.
Arguments
Number (required) The (at most) 10-digit hexadecimal number that is to be converted into a binary number in two’s complement notation (see the section titled Two’s Complement). The valid entry values for Number are FFFFFFFE00 (–512decimal) through 1FF (+511decimal). If Number has a negative value, a 10-digit binary number is returned.
Places (optional) Determines how many digits of the binary number are displayed, and is used to display leading zeros in the result. If the argument Places is omitted, only the required number of digits is displayed. Possible decimal places after the decimal point are ignored.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Caution
Excel functions return the obtained binary numbers as text in the default format (left-aligned). Therefore, they cannot be used for calculations with other formulas without special provisions. For further processing, text functions should be used, even though the result looks like a number.
If formulas with arithmetic operators process references to these binary numbers, Excel does not generate error messages but rather interprets them as decimal numbers, which of course leads to false results.
Examples. Here are examples that illustrate this function.
=HEX2BIN("E")returns1110.=HEX2BIN("E",6)returns001110.=HEX2BIN("155")returns101010101.=HEX2BIN("FFFFFFFFFF")returns1111111111(corresponds to –1decimal).=HEX2BIN("FFFFFFFFFF",6)also returns1111111111(corresponds to –1decimal).
The worksheet in Figure 17-3 contains many additional examples, including the conversion into the decimal and octal systems.
Syntax. HEX2DEC(Number)
Definition. This function converts a hexadecimal number into a decimal number.
Arguments
Number (required) The (at most) 10-digit hexadecimal number in two’s complement notation that is to be converted into a decimal number. The valid entry values for Number are 8000000000 to 7FFFFFFFFF (that is, from –549,755,813,888decimal through +549,755,813,887decimal).
The (optional) Places argument is not necessary and not available, because the goal to display leading zeros can easily be reached by formatting the cells.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Examples. The following examples show how to use HEX2DEC().
=HEX2DEC("E")returns14.=HEX2DEC("155")returns341.=HEX2DEC("FFFFFFFFFF")returns–1.
You can find additional examples in the examples for HEX2BIN() in HEX2BIN().
Syntax. HEX2OCT(Number,Places)
Definition. This function converts a hexadecimal number into an octal number.
Arguments
Number (required) The (at most) 10-digit binary number in two’s complement notation that is to be converted into an octal number. The valid entry values for Number are FFE0000000 (negative) to 1FFFFFFF (positive).
Places (optional) Determines how many digits are to be displayed, and is used to display leading zeros in the result. If the argument Places is omitted, only the required number of digits is displayed. Possible decimal places after the decimal point are ignored.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Caution
The octal numbers returned by this function are strings that cannot readily be used like common numbers in other formulas. If an octal number is used in a formula with arithmetic operators, the number is interpreted as a decimal number. For example, if the cells C13 and C14 contain the octal numbers 320 and 377, the formula
=C13+C14
returns the value 697, which is false. It already becomes obvious from the number 697, which is not a valid octal number, because the digit range of the octal system is only 0 through 7, and the number 9 is not part of it. The octal number 717 would be correct.
The following formula works correctly:
=DEC2OCT(OCT2DEC(C13)+OCT2DEC(C14))
Examples. The following examples illustrate this function.
=HEX2OCT("E")returns16.=HEX2OCT("E",4)returns0016.=HEX2OCT("155")returns525.=HEX2OCT("155",4)returns0525.=HEX2OCT("FFFFFFFFFF")returns7777777777(corresponds to –1decimal).
You can find additional examples in the examples for HEX2BIN() in HEX2BIN().
Syntax. OCT2BIN(Number,Places)
Definition. This function converts an octal number into a binary number.
Arguments
Number (required) The (at most) 10-digit octal number in two’s complement notation (see the section titled Two’s Complement earlier in this chapter that is to be converted into a binary number.
Places (optional) Determines how many digits of the binary number are displayed, and is used to display leading zeros in the result. If the argument Places is omitted, only the required number of digits is displayed. Possible decimal places after the decimal point are ignored.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Caution
Excel functions return the obtained binary numbers as text in the default format (left-aligned). Therefore, they cannot be used for calculations with other formulas without special provisions. For further processing, text functions should be used, even though the result looks like a number.
If formulas with arithmetic operators process references to these binary numbers, Excel does not generate error messages but rather interprets them as decimal numbers, which of course leads to false results.
Examples. The following examples illustrate this function.
=OCT2BIN(16)returns1110.=OCT2BIN(16,6)returns001110.=OCT2BIN(525)returns101010101.=OCT2BIN(7777777777)returns1111111111(corresponds to –1decimal).
The worksheet in Figure 17-4 contains many additional examples, including the conversion into the decimal and hexadecimal systems.
Syntax. OCT2DEC(Number)
Definition. This function converts an octal number into a decimal number.
Argument
Number (required) The (at most) 10-digit octal number in two’s complement notation that is to be converted into a decimal number
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Examples. The following examples illustrate OCT2DEC().
=OCT2DEC(16)returns14.=OCT2DEC(525)returns341.=OCT2DEC(7777777777)returnsFFFFFFFFFF(corresponds to –1decimal).=OCT2DEC(7777777777,4))also returnsFFFFFFFFFF(corresponds to –1decimal).
You can find additional examples in the examples for OCT2BIN() in OCT2BIN().
Syntax. OCT2HEX(Number,Places)
Definition. This function converts an octal number into a hexadecimal number.
Arguments
Number (required) The (at most) 10-digit octal number in two’s complement notation (see the section titled Two’s Complement) that is to be converted into a hexadecimal number. If Number has a negative value, a 10-digit hexadecimal number is returned.
Places (optional) Determines how many digits are to be displayed, and is used to display leading zeros in the result. If the argument Places is omitted, only the required number of digits is displayed. Possible decimal places after the decimal point are ignored.
Background. See the detailed description in the section titled Number Systems in the introduction to these functions (Number Systems).
Caution
The result of OCT2HEX() is a string that cannot be used for additional calculations. If necessary, you can use text functions for the returned hexadecimal numbers. You should pay special attention to the fact that formulas that link references to the hexadecimal numbers with arithmetic operators do not generate error messages, as long as those numbers only contain digits from the decimal system (0 through 9). In this case, they are interpreted as decimal numbers, and the formula returns an unintended result.
The following example illustrates a special case:
=OCT2HEX(1750) returns 3E8 as a hexadecimal number. If this value is used through a cell reference in a formula, it can be interpreted as 3E+08, that is, as a completely different number in scientific format.
Examples. The following examples illustrate OCT2HEX():
=OCT2HEX(16)returnsE.=OCT2HEX(16,2)returns0E.=OCT2HEX(525)returns155.=OCT2HEX(7777777777)returnsFFFFFFFFFF(corresponds to –1decimal).=OCT2HEX(7777777777,4)also returnsFFFFFFFFFF(corresponds to –1decimal).
You can find additional examples in the examples for OCT2BIN() in OCT2BIN().
Syntax. CONVERT(Number,From_Measurement_Unit,To_Measurement_Unit)
Definition. This function performs conversions between different measurement systems.
Arguments
Number (required) The value to be converted in From_Measurement_Unit
From_Measurement_Unit (required) The unit of the initial number
To_Measurement_Unit (required) The unit into which you want to convert
CONVERT() accepts for From_Measurement_Unit and To_Measurement_Unit the text values listed in tables in this section as measurement units, which must be put within quotation marks. If these arguments contain cell references, the text in the included cells do not need to be put into quotation marks, because the rules implemented in Excel also mark these text values as text without quotation marks. Pay attention to capitalization! If you don’t, you might get the error message #NV.
Some measurement units can also be written differently. In Table 17-6 through Table 17-15, the various measurement systems are sorted by category.
Note
A slug is a mass unit within the British Gravitational System (BG) and the English Engineering System (EE).
Table 17-6. (Commercial) Weight Units of the CONVERT() Function
Weight and Mass | Abbreviations for Measurement Units |
|---|---|
Gram | g |
Slug | sg |
Pound (commercial weight) | lbm |
u (atomic mass unit) | u |
Ounce (commercial weight) | ozm |
Table 17-7. Length Units of the CONVERT() Function
Distance | Abbreviations for Measurement Units |
|---|---|
Meter | m |
Statute mile | mi |
Nautical mile | Nmi |
Inch | in |
Foot | ft |
Yard | yd |
Angstrom | ang |
Pica (1/72 inches) | pica |
Table 17-8. Time Units of the CONVERT() Function
Time | Abbreviations for Measurement Units |
|---|---|
Year | yr |
Day | day |
Hour | hr |
Minute | mn |
Second | sec |
Table 17-10. Force Units of the CONVERT() Function
Force | Abbreviations for Measurement Units |
|---|---|
Newton | N |
Dyne | dyn, dy |
Pound force | lbf |
Table 17-11. Energy Units of the CONVERT() Function
Energy | Abbreviations for Measurement Units |
|---|---|
Joule | J |
Erg | e |
Thermodynamic calorie | c |
IT calorie | cal |
Electron volt | eV, ev |
Horsepower-hour | HPh, hh |
Watt-hour | Wh, wh |
Foot-pound | flb |
BTU | BTU, btu |
Table 17-12. Performance Units of the CONVERT() Function
Performance | Abbreviations for Measurement Units |
|---|---|
Horsepower | HP, h |
Watt | W, w |
Table 17-14. Temperature Units of the CONVERT() Function
Temperature | Abbreviations for Measurement Units |
|---|---|
Degree Celsius (°C) | C, cel |
Degree Fahrenheit (°F) | F, fah |
Kelvin (K) | K, kel |
Table 17-15. Liquid Measurement Units of the CONVERT() Function
Liquid Measures | Abbreviations for Measurement Units |
|---|---|
Teaspoon | tsp |
Tablespoon | tbs |
Fluid ounce | oz |
Cups | cup |
U.S. pint | pt, us_pt |
U.K. pint | uk_pt |
Quart | qt |
Gallon | gal |
Liter | l, lt |
Table 17-16 lists the increase and decrease factors as well as the unit prefixes. The abbreviated unit prefixes can be used for all metric specifications of From_Measurement_Unit and To_Measurement_Unit.
Table 17-16. Unit Prefixes
Prefix | Multiplier | Abbreviation |
|---|---|---|
Exa | 1E+18 (1018) | E |
Peta | 1E+18 (1015) | P |
Tera | 1E+18 (1012) | T |
Giga | 1E+18 (109) | G |
Mega | 1E+18 (106) | M |
Kilo | 1E+18 (103) | k |
Hecto | 1E+18 (102) | h |
Deka | 1E+18 (101) | e |
Deci | 1E–01 (10–1) | d |
Centi | 1E–01 (10–2) | c |
Milli | 1E–01 (10–3) | m |
Micro | 1E–01 (10–6) | u |
Nano | 1E–01 (10–9) | n |
Pico | 1E–01 (10–12) | p |
Femto | 1E–01 (10–15) | f |
Atto | 1E–01 (10–18) | a |
Background. Traditionally, nonmetric measurements are used in the United States—that is, measurements that are not based on the centimeter-gram-second (CGS) system. In scientific, commercial, and official communication on an international level, only the international units system (SI system) is used, which is becoming more prominent in the United States as well. The conversion between the U.S. and the international units isn’t always easy. The CONVERT() function can save you a lot of work here.
CONVERT() can change a metric length specification into yards (and the other way around). However, the units you want to convert must come from the same category. For example, you obviously cannot convert time specifications into length measurements.
Examples. For each possibility of this function, you can find an example in Figure 17-5.
