Engineering functions – A to C

1. How to use the BIN2DEC function

The BIN2DEC function converts a binary number to decimal.

What is a binary number?
The binary system is a positional numeral system that uses only two digits: 0 and 1. The binary system is important in our society, many devices like computers, digital cameras, mobile phones and modern cars use binary code to store, process and communicate data. The binary numeral system makes it easy to store and transmit data using binary digits or bits.

Decimal	Binary
0	0
1	1
2	10
3	11
4	100
5	101
6	110
7	111
8	1000
9	1001
10	1010
11	1011

What is a decimal number?
The decimal system is a positional numeral system that uses 10 as the base, it requires 10 different numerals: 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. The dot or the decimal point represents decimal fractions which are not whole numbers.

The decimal number 520 has three positions, each with a different weight. It starts with 10^0 on the right and increases by one power on each additional position to the left.

520 = (5*10^2)+(2*10^1)+(0*10^0)

520 = 500 + 20 + 0

1. BIN2DEC Function Syntax

BIN2DEC(number)

Back to top

2. BIN2DEC Function Arguments

number

Required. The binary number you want to convert. The sign bit is the most significant bit of number, the following 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

What is a sign bit?

The sign bit indicates whether a binary number is positive or negative, if the bit is 0 the number is positive, if the bit is 1, the number is negative.

What is a magnitude bit?

The remaining 9 bits are magnitude bits which represents the absolute value of the number. An absolute number is a number without the sign.

What is two's-complement notation?

Two’s-complement notation is when the magnitude bits are changed from 0 to 1 and 1 to 0 and adding 1 to the result, negative numbers are represented using two’s-complement notation. For example:

Decimal number +9 using 10 bits is 0000001001.
Decimal number -9 using 10 bits is 111110110 + 1 = 111110111

Back to top

3. BIN2DEC Function example

The image above demonstrates the BIN2DEC function cell C3, C4, C5, and C6. It calculates the decimal values based on the corresponding cells which contain different binary numbers.

Formula in cell B3:

The first cell B3 contains 11001 and the BIN2DEC function returns 25 in cell C3, the second cell B4 contains 11 and the BIN2DEC function returns 3.

The first cell B5 contains 100 and the BIN2DEC function returns 4 in cell C3, the second cell B6 contains 11111111 and the BIN2DEC function returns 255.

The next section describes how these values are calculated in detail.

Back to top

4. How is the BIN2DEC function calculated in detail?

The binary system is also called the base-2 system because each digit represents a power of 2. The reason each digit represents a power of 2 is the position in a binary number has a weight that is a power of 2 starting from 2^0 on the right and increasing by one power on each additional position to the left.

Example 1

For example, the binary number 11001 has five positions, each with a different weight:

11001  = (1*2^4)+(1*2^3)+(0*2^2)+(0*2^1)+(1*2^0)

16+8+1 = 25

Example 2

11 = (1*2^1)+(1*2^0)

2+1 = 3

Example 3

100 = (1*2^2)+(0*2^1)+(0*2^0)

4+0+0 = 4

Example 4

11111111 = (1*2^7)+(1*2^6)+(1*2^5)+(1*2^4)+(1*2^3)+(1*2^2)+(1*2^1)+(1*2^0)

128+64+32+16+8+4+2+1 = 255

Back to top

5. BIN2DEC function not working

Maximum binary digits are 10 characters, in other words, the argument cannot contain more than 10 digits (10 bits). The BIN2DEC function will return a #NUM! error if this limit is exceeded.

Back to top

6. How to convert binary 8 bit to the ANSI character set

The ANSI system is an extended version of the ASCII system often used in Microsoft windows, it adds another 128 characters. ASCII assigns standard numeric values to letters, numbers, and other characters used in computers. For example, the letter A has the value 65, the digit 0 has the value 48, and the period (.) has the value 46.

ASCII uses seven-bit binary numbers to represent these values. Since there are 128 different possible combinations of seven 0s and 1s, the code can represent 128 different characters. The ANSI encoding adds another 128 characters, see the image above. For example, the letter A is represented by the binary number 01000001, and the digit 0 is represented by the binary number 00110000.

This formula converts a single 8 bit binary number to its equivalent character in the ANSI system.

Formula in cell C3:

Explaining formula

Step 1 - Convert binary to decimal

The BIN2DEC function converts a binary number to the decimal number system.

Function syntax: BIN2DEC(number)

BIN2DEC(B3)

becomes

BIN2DEC("01000001")

and returns 65.

Step 2 - Convert decimal to ANSI character

The CHAR function converts a number to the corresponding ANSI character determined by your computers character set.

Function syntax: CHAR(text)

CHAR(BIN2DEC(B3))

becomes

CHAR(65)

and returns

"A".

Back to top

7. Convert multiple binary digit groups

This example demonstrates how to convert multiple 8 bit binary strings to characters, the binary strings are separated by a blank (space).

Formula in cell C3:

For example, 01001000 has 8 bits. The corresponding decimal number is 72 which is character "H" in the ANSI system.

Explaining formula

Step 1 - Split string based on a delimiter

The TEXTSPLIT function splits a string into an array based on delimiting values.

Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(B3," ")

becomes

TEXTSPLIT("01001000 01100101 01101100 01101100 01101111"," ")

and returns

{"01001000", "01100101", "01101100", "01101100", "01101111"}.

Step 2 - Convert binary to decimal

The BIN2DEC function converts a binary number to the decimal number system.

Function syntax: BIN2DEC(number)

BIN2DEC(TEXTSPLIT(B3," "))

becomes

BIN2DEC({"01001000", "01100101", "01101100", "01101100", "01101111"})

and returns

{72,101,108,108,111}

Step 3 - Convert decimal to ANSI character

The CHAR function converts a number to the corresponding ANSI character determined by your computers character set.

Function syntax: CHAR(text)

CHAR(BIN2DEC(TEXTSPLIT(B3," ")))

becomes

CHAR({72,101,108,108,111})

and returns

{"H","e","l","l","o"}

Step 4 - Join characters

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN("",1,CHAR(BIN2DEC(TEXTSPLIT(B3," "))))

becomes

TEXTJOIN,"",1,{"H","e","l","l","o"})

and returns

"Hello".

Back to top

Back to top

Useful links

BIN2DEC Function - Microsoft
Binary to Decimal converter
How to Convert from Binary to Decimal

2. How to use the BIN2HEX function

The BIN2HEX function converts a binary number to a hexadecimal number.

What is the binary system?

The binary system is a positional numeral system that uses only two digits: 0 and 1. The binary system is important in our society, many devices like computers, digital cameras, mobile phones and modern cars use binary code to store, process and communicate data. The binary numeral system makes it easy to store and transmit data using binary digits or bits.

What is a bit?

A bit is a binary digit. It is the most basic unit of information in binary communication and computing. The bit can either be the value 0 (zero) or 1, a bit can also be part of a larger sequence of bits that can represent numbers, characters, and other types of data.

The following table shows the binary, decimal and hexadecimal values from 0 (zero) to 17.

Binary	Decimal	Hexadecimal
00000000	0	0
00000001	1	1
00000010	2	2
00000011	3	3
00000100	4	4
00000101	5	5
00000110	6	6
00000111	7	7
00001000	8	8
00001001	9	9
00001010	10	A
00001011	11	B
00001100	12	C
00001101	13	D
00001110	14	E
00001111	15	F
00010000	16	10
00010001	17	11

What is a hexadecimal number?

A hexadecimal number is a number with a base of 16, for example, the decimal system uses a base of 10. This means that each digit in a hexadecimal number can have 16 possible values, from 0 to 15, however, the letters A to F are used from 10 to 15. See the hexadecimal column in the table above.

Hexadecimal numbers are often used in computers, the reason is they represent four binary digits (bits) with one hexadecimal digit. For example, the binary number 1010 is equivalent to the hexadecimal number A.

Hexadecimals make it easier to write big numbers with less digits, in other words, hexadecimals shorten binary digits considerably. For example, we can use hexadecimal to show the values of colors and MAC addresses in computers.

1. BIN2HEX function Syntax

BIN2HEX(number,[places])

Back to top

2. BIN2HEX function Arguments

number	Required. The binary number you want to convert to hexadecimal. The sign bit is the most significant bit of number, the following 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.
[places]	Optional. The number of characters to use. If not entered the minimum number of characters is used. Use this argument to add leading 0 (zeros).

What is a sign bit?

The sign bit indicates whether a binary number is positive or negative, if the bit is 0 the number is positive, if the bit is 1, the number is negative.

What is a magnitude bit?

The remaining 9 bits are magnitude bits which represents the absolute value of the number. An absolute number is a number without the sign.

What is two's-complement notation?

Two’s-complement notation is used to represent negative numbers, the magnitude bits are changed from 0 to 1 and 1 to 0 and adding 1 to the result. For example:

Decimal number +9 using 10 bits is 0000001001.
Decimal number -9 using 10 bits is 1111110110 + 1 = 1111110111

Back to top

3. BIN2HEX function example

The image above demonstrates the BIN2HEX function in cells C3, C4, C5, and C6. It calculates the hexadecimal values based on the corresponding cells which contain different binary numbers.

Formula in cell B3:

The first cell B3 contains 0000011001 and the BIN2HEX function returns 19 in cell C3, the second cell B4 contains 0000000011 and the BIN2HEX function returns 3.

The third cell B5 contains 0000000100 and the BIN2HEX function returns 4 in cell C3, the fifth cell B6 contains 0011111111 and the BIN2HEX function returns FF.

The next section describes how these values are calculated in detail.

Back to top

4. How is the BIN2HEX function calculated in detail?

Binary	Hexadecimal
0000	0
0001	1
0010	2
0011	3
0100	4
0101	5
0110	6
0111	7
1000	8
1001	9
1010	A
1011	B
1100	C
1101	D
1110	E
1111	F

1. Split the binary number into groups of four digits starting from the right.
2. If the last group has less than four digits, add zeros to the left to make it four digits.
3. Use the table above to find the corresponding hexadecimal digit for each group of four digits.

Back to top

5. BIN2HEX function not working

BIN2HEX returns the #NUM! error value if

• more than 10 binary digits are used. See cells B3 and C3 in the image above.
• the number is not a valid binary number. See cells B4 and C4 in the image above.
• [places] is negative. See the formula next to cell C5.
• it requires more than the specified places characters. See the formula next to cell C6.

BIN2HEX returns the #VALUE! error value if places is not a number. See the formula next to cell C7.

The second argument [places]is truncated if it is not an integer. For example, [places] is 4.5 and the BIN2HEX function truncates it to 4. See the formula next to cell C8.

BIN2HEX ignores places and returns a 10-character hexadecimal number if the binary number is negative.

Back to top

Back to top

Useful links

BIN2HEX function - Microsoft
Binary to Hex converter
How to Convert Binary to Hexadecimal?

3. How to use the BIN2OCT function

The BIN2OCT function converts a binary number to octal.

What is the binary system?

The binary system is a positional numeral system that uses only two digits: 0 and 1. The binary system is important in our society, many devices like computers, digital cameras, mobile phones and modern cars use binary code to store, process and communicate data. The binary numeral system makes it easy to store and transmit data using binary digits or bits.

What is a bit?

A bit is a binary digit. It is the most basic unit of information in binary communication and computing. The bit can either be the value 0 (zero) or 1, a bit can also be part of a larger sequence of bits that can represent numbers, characters, and other types of data.

The following table shows the binary, decimal and octal values from 0 (zero) to 17.

Binary	Decimal	Octal
00000000	0	0
00000001	1	1
00000010	2	2
00000011	3	3
00000100	4	4
00000101	5	5
00000110	6	6
00000111	7	7
00001000	8	10
00001001	9	11
00001010	10	12
00001011	11	13
00001100	12	14
00001101	13	15
00001110	14	16
00001111	15	17
00010000	16	20
00010001	17	21

What is the octal system?

The octal system is a number system with a base of 8 that uses the digits 0, 1, 2, 3, 4, 5, 6 and 7. The octal system is often used in electronics because it is easy to perform a conversion between octal and binary numbers.

1. BIN2OCT function Syntax

BIN2OCT(number,[places])

Back to top

2. BIN2OCT function Arguments

number	Required. The binary number you want to convert to octal. The sign bit is the most significant bit of number, the following 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation. Maximum binary values are 10 characters.
[places]	Optional. The number of characters to use. If not entered the minimum number of characters is used. Use this argument to add leading 0 (zeros).

What is a sign bit?

The sign bit indicates whether a binary number is positive or negative, if the bit is 0 the number is positive, if the bit is 1, the number is negative.

What is a magnitude bit?

The remaining 9 bits are magnitude bits which represents the absolute value of the number. An absolute number is a number without the sign.

What is two's-complement notation?

Two’s-complement notation is used to represent negative numbers, the magnitude bits are changed from 0 to 1 and 1 to 0 and adding 1 to the result. For example:

Decimal number +9 using 10 bits is 0000001001.
Decimal number -9 using 10 bits is 1111110110 + 1 = 1111110111

Back to top

3. BIN2OCT function example

The image above shows the BIN2OCT function in cells C3, C4, C5, and C6. It calculates the octal values based on the corresponding cells which contain different binary numbers.

Formula in cell C3:

The first cell B3 contains 0000011001 and the BIN2OCT function returns 31 in cell C3, the second cell B4 contains 0000000011 and the BIN2OCT function returns 3.

The third cell B5 contains 0000000100 and the BIN2OCT function returns 4 in cell C3, the fifth cell B6 contains 0011111111 and the BIN2OCT function returns 377.

The next section describes how these values are calculated in detail.

Back to top

4. How is the BIN2OCT function calculated in detail?

Follow these steps in order to convert from binary to octal:

1. Divide the binary number into groups of three bits starting from the rightmost bit.
2. Add leading zeros to make it a full group if the leftmost group contains less than three bits.
3. Find the corresponding octal digit for each group using this table:

Binary	Decimal	Octal
000	0	0
001	1	1
010	2	2
011	3	3
100	4	4
101	5	5
110	6	6
111	7	7

4. Write the octal digits in the same order as the binary groups.

Back to top

5. BIN2OCT function not working

BIN2OCT returns the #NUM! error value if

• more than 10 binary digits are used. See cells B3 and C3 in the image above.
• the number is not a valid binary number. See cells B4 and C4 in the image above.
• [places] is negative. See the formula next to cell C5.
• it requires more than the specified places characters. See the formula next to cell C6.

BIN2OCT returns the #VALUE! error value if places is not a number. See the formula next to cell C7.

The second argument [places]is truncated if it is not an integer. For example, [places] is 4.5 and the BIN2OCT function truncates it to 4. See the formula next to cell C8.

BIN2OCT ignores places and returns a 10-character hexadecimal number if the binary number is negative.

Back to top

Back to top

Useful links

BIN2OCT function - Microsoft
Octal numeral system - Wikipedia
Octal Number System - Meaning, Conversion, Solved Examples, Practice Questions (cuemath.com)

4. How to use the BITAND function

What is the BITAND function?

The BITAND function calculates a bitwise 'AND' of two decimal numbers. Note, it also returns a decimal number.

What is a decimal number?

The decimal system is a positional numeral system that uses 10 as the base, it requires 10 different numerals: 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. The dot or the decimal point represents decimal fractions which are not whole numbers.

The decimal number 520 has three positions, each with a different weight. It starts with 10^0 on the right and increases by one power on each additional position to the left.

520 = (5*10^2)+(2*10^1)+(0*10^0)

520 = 500 + 20 + 0

What is a bit?

The binary system is a positional numeral system that uses only two digits: 0 and 1. The binary system is important in our society, many devices like computers, digital cameras, mobile phones and modern cars use binary code to store, process and communicate data. The binary numeral system makes it easy to store and transmit data using binary digits or bits.

The following table shows decimal numbers from 0 to 11 and the binary equivalent:

Decimal	Binary
0	0
1	1
2	10
3	11
4	100
5	101
6	110
7	111
8	1000
9	1001
10	1010
11	1011

What is bitwise?

Bitwise operations are performed on the binary representation of numbers, where each bit has a value of either 0 or 1. Some common bitwise operations are AND, OR, XOR, NOT and SHIFT. They can be used for masking, toggling, swapping, testing or arithmetic. This article demonstrates AND operations.

What is an AND operation?

The BITAND function performs AND logic bit by bit on the numbers based on their binary representation. AND logic means that the value of each bit position is counted only if both parameter's bits at that position are 1.

The following operations show that AND logic is the same as multiplying binary numbers:

0*0=0
1*0=0
0*1=0
1*1=1

Example, the table below shows bitwise AND logic between two random binary numbers.

Bit position	3	2	1	0
Binary value 1	1	0	0	1
Binary value 2	0	1	0	1
AND result	0	0	0	1

Bit position 0 is the only operation that has 1 in both bits, the remaining bits result in 0 (zero).

1. BITAND function Syntax

BITAND(number1, number2)

2. BITAND function Arguments

number1	Required. The first number.
number2	Required. The second number.

Back to top

3. BITAND function example

The image above shows the BITAND function cell D3, it has two arguments number 1 and number2 which are specified in cells B3 and B4.

Formula in cell B3:

The formula in cell B3 performs AND logic for each bit between decimal numbers and returns a decimal number.

Back to top

4. How is the BITAND function calculated in detail?

Here are the steps to perform bitwise AND logic:

1. Convert both decimal numbers to binary.
2. Perform bitwise AND logic.
3. Convert binary output back to decimal again.

Example 1,

Decimal number 5 is 0000 0101 in binary and decimal number 9 is 0000 1001 in binary.

With AND logic bitwise the result is 0000 0001 which is number 1.

Example 2,

Decimal number 45 is 0010 1101 in binary and decimal number 21 is 0001 0101 in binary.

Bit position	7	6	5	4	3	2	1	0
Binary value 1	0	0	1	0	1	1	0	1
Binary value 2	0	0	0	1	0	1	0	1
AND result	0	0	0	0	0	1	0	1

The bitwise AND logic results in 0000 0101 which is the decimal number 5.

5. BITAND function not working

The BITAND function returns a #NUM! error if

• argument number is 2^48 = 2.81475E+14 or larger. See row 4 in the image above.
• argument number is negative. See row 3 in the image above.

The BITAND function returns a #VALUE! error if the argument is a letter. See row 5 in the image above.

The BITAND function seems to work with boolean values TRUE and FALSE. See row 6 in the image above.

Back to top

6. How to perform bitwise AND operations between binary numbers?

The following example demonstrates a formula that lets you calculate bitwise AND logic between two binary numbers, the result is also binary.

Formula:

Explaining formula

Step 1 - Convert binary to decimal

The BIN2DEC function converts a binary number to the decimal number system.

Function syntax: BIN2DEC(number)

BIN2DEC(B3)

becomes

BIN2DEC("00000101")

and returns 5.

Step 2 - Perform bitwise AND operation

The BITAND function calculates a bitwise 'AND' of two numbers.

Function syntax: BITAND(number1, number2)

BITAND(BIN2DEC(B3),BIN2DEC(C3))

becomes

BITAND(5,101)

and returns 5.

Step 3 - Convert result to back to binary

The DEC2BIN function converts a decimal number to a binary number.

Function syntax: DEC2BIN(number, [places])

DEC2BIN(BITAND(BIN2DEC(B3),BIN2DEC(C3)))

becomes

DEC2BIN(5)

and returns "00000101".

Back to top

Back to top

Useful resources

BITAND function - Microsoft
Bitwise operation - Wikipedia

5. How to use the BITLSHIFT function

What is the BITLSHIFT function?

The BITLSHIFT function calculates a decimal number whose binary representation is shifted left by a specified number of bits.

What is the binary system?

The binary system is a positional numeral system that uses only two digits: 0 and 1. The binary system is important in our society, many devices like computers, digital cameras, mobile phones and modern cars use binary code to store, process and communicate data. The binary numeral system makes it easy to store and transmit data using binary digits or bits.

The following table shows decimal numbers from 0 to 11 and the binary equivalent:

Decimal	Binary
0	0000
1	0001
2	0010
3	0011
4	0100
5	0101
6	0110
7	0111
8	1000
9	1001
10	1010
11	1011

What are bits?

A bit is a binary digit meaning the single digit in a binary number. Binary numbers are often split into four bits, four bits represents decimal numbers from 0 to 15 or hexadecimal numbers from 0 to F. For example, binary number 0010 has four bits.

What is bits shifted left?

Each bit is moved one position to the left, the easiest way to do this is to add a zero on the right side of the binary number.

The result of shifting bits left is the same as multiplying the original value by a power of two. For example, shift decimal value 10 left by one position we get 20 which is 10 times 2. If we shift it left by two positions we get 40 which is 10 * 2 * 2

Decimal value 10 is 1010 in the binary system. Add a zero to the right side and we get 10100 which is 20 in the decimal system.

Why shift bits left?

Bits shifted left can be used for performing arithmetic operations, manipulating bits, or encoding data. However, the BITLSHIFT function works only for positive decimal numbers. It is therefore not possible to change the significant bit when shifting left.

What are significant bits?

The significant bit or sign bit indicates whether a binary number is positive or negative, if the bit is 0 the number is positive, if the bit is 1, the number is negative.

What is a magnitude bit?

The remaining bits are magnitude bits which represents the absolute value of the number. An absolute number is a number without the sign.

 

1. BITLSHIFT Function Syntax

BITLSHIFT(number, shift_amount)

2. BITLSHIFT Function Arguments

number	Required. The number you want to shift. Must be 0 (zero) or greater than 0 (zero).
shift_amount	Required. How many zeros you want to add to the right side. This number can be negative as well,

3. BITLSHIFT function example

The image above demonstrates a formula in cell D3 that shifts bits left based on a decimal number specified in cell B3, the number of positions shifted left is specified in cell C3.

Formula in cell D3:

Example 1,

Cell B3 contains decimal number 5 which is 0101 in the binary system. Add a 0 (zero) to the right side and you get 1010 binary which is number 10 in the decimal system.

Example 2,

Cell B8 contains decimal number 41 which is 0010 1001 in the binary system, cell C8 contains 1 which means that the binary digits are shifted left by one position. Add a 0 (zero) to the right side and you get 0101 0010 in binary which is number 82 in the decimal system.

Back to top

4. How is the BITLSHIFT function calculated in detail?

The table below shows how bits are shifted left using the BITLSHIFT function by one position.

Bit position	3	2	1	0
Binary value 5	0	1	0	1
BITLSHIFT result	1	0	1	0

Simply add a 0 (zero) to the right side of the binary number to shift bits one step, this applies to multiple steps as well.

For example, to shift bits three steps left add the same amount of 0's (zeros) to the right side of the binary number, in this example three 0's (zeros).

Decimal number 5 is 0101 in the binary system, shifting bits 3 positions results in this binary number: 0010 1000 which is equal to decimal number 40.

Back to top

5. BITLSHIFT function not working

The BITLSHIFT function returns a #NUM! error if

• argument number is 2^48 = 2.81475E+14 or larger. See row 4 in the image above.
• argument number is negative. See row 3 in the image above.
• argument number is smaller than 0 (zero).

The BITLSHIFT function returns a #VALUE! error if either of the argument is a letter. See row 5 in the image above.

The BITLSHIFT function seems to work with boolean values TRUE and FALSE. See row 6 in the image above.

Back to top

6. How to shift bits using binary numbers?

The BITLSHIFT function needs a decimal number to be able to shift the binary representation left and the return the decimal representation of the shifted bits.

The following formula lets you shift binary numbers based on a given number of shifts, the result is also a binary number.

Formula in cell D3:

The formula is limited by the BIN2DEC and DEC2BIN functions, they can only handle decimal numbers from -512 to 511.

The following formula works for larger numbers:

However, this may create a new problem with the sign bit that shows if a binary number is positive or negative.

Explaining formula

Step 1 - Convert binary number to decimal the system

The BIN2DEC function converts a binary number to the decimal number system.

Function syntax: BIN2DEC(number)

BIN2DEC(B3)

becomes

BIN2DEC("00000101")

and returns 5

Step 2 - Shift bits to the left

The BITLSHIFT function calculates a number whose binary representation is shifted left by a specified number of bits.

Function syntax: BITLSHIFT(number, shift_amount)

BITLSHIFT(BIN2DEC(B3),C3)

becomes

BITLSHIFT(5,1)

and returns 10

Step 3 - Convert result to binary

The DEC2BIN function converts a decimal number to a binary number.

Function syntax: DEC2BIN(number, [places])

DEC2BIN(BITLSHIFT(BIN2DEC(B3),C3))

becomes

DEC2BIN(10)

and returns

"1010".

Back to top

Useful resources

BITLSHIFT function - Microsoft support
Bit shifts - Wikipedia

6. How to use the BITOR function

What is the BITOR function?

The BITOR function performs a bitwise 'OR' of two decimal numbers, it returns a decimal number as well.

What is a decimal number?

The decimal system is a positional numeral system that uses 10 as the base, it requires 10 different numerals: 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. The dot or the decimal point represents decimal fractions which are not whole numbers.

The decimal number 520 has three positions, each with a different weight. It starts with 10^0 on the right and increases by one power on each additional position to the left.

520 = (5*10^2)+(2*10^1)+(0*10^0)

520 = 500 + 20 + 0

What is a bit?

The binary system is a positional numeral system that uses only two digits: 0 and 1. The binary system is important in our society, many devices like computers, digital cameras, mobile phones and modern cars use binary code to store, process and communicate data. The binary numeral system makes it easy to store and transmit data using binary digits or bits.

The following table shows decimal numbers from 0 to 11 and the binary equivalent:

Decimal	Binary
0	0
1	1
2	10
3	11
4	100
5	101
6	110
7	111
8	1000
9	1001
10	1010
11	1011

What is bitwise?

Bitwise operations are performed on the binary representation of numbers, where each bit has a value of either 0 or 1. Some common bitwise operations are AND, OR, XOR, NOT and SHIFT. They can be used for masking, toggling, swapping, testing or arithmetic. This article demonstrates OR operations.

What is an OR operation?

The BITOR function performs OR logic bit by bit on the numbers based on their binary representation. OR logic means that the value of each bit position is counted only if at least one parameter's bits at that position are 1.

The following operations show that OR logic is the same as adding binary numbers:

0+0=0
1+0=1
0+1=0
1+1=1

Example, the table below shows bitwise OR logic between two random binary numbers.

Bit position	3	2	1	0
Binary value 1	1	0	0	1
Binary value 2	0	1	0	1
OR result	1	1	0	1

Bit position 1 is the only operation that has 0 in both bits, the remaining bits result in 1.

1. BITOR Function Syntax

BITOR(number1, number2)

2. BITOR Function Arguments

number1	Required. The first number.
number1	Required. The second number.

3. BITOR Function example

The image above demonstrates the BITOR function in cell D3, the arguments are in cells B3 and B4 respectively.

Formula in cell D3:

Cells C3 and C4 shows the binary representation of the decimal numbers in cells B3 and B4. The BITOR function in cell D3 returns 13 from the decimal numbers 5 and 9.

The second example is demonstrated in cell D8:

The BITOR function in cell D8 returns 61 from decimal numbers 45 and 21.

The next sections explains how bitwise OR logic works.

Back to top

4. How is the BITOR function calculated in detail?

Here are the steps to perform bitwise OR logic:

1. Convert both decimal numbers to binary.
2. Perform bitwise OR logic.
3. Convert binary output back to decimal again.

Example 1

Number 5 is 0000 0101 in binary and number 9 is 0000 1001. If at least one bit is 1 the returning digit is 1.

101 + 1101 = 1101. 1101 is the decimal number 13.

Example 2

Number 45 is 0010 1101 in binary and number 21 is 0001 0101. If at least one bit is 1 the returning digit is 1.

Bit position	7	6	5	4	3	2	1	0
Decimal number 45	0	0	1	0	1	1	0	1
Decimal number 21	0	0	0	1	0	1	0	1
OR result	0	0	1	1	1	1	0	1

The bitwise OR operation results in 0011 1101 which is decimal number 61.

Back to top

5. BITOR Function not working

The BITOR function returns a #NUM! error if

• argument number is 2^48 = 2.81475E+14 or larger. See row 4 in the image above.
• argument number is negative. See row 3 in the image above.

The BITOR function returns a #VALUE! error if the argument is a letter. See row 5 in the image above.

The BITAND function seems to work with boolean values TRUE and FALSE. See row 6 in the image above.

Back to top

6. How to perform bitwise OR operations between binary numbers?

The following formula lets you perform bitwise OR logic based on binary numbers, the result is also a binary number.

Formula:

Cells C3 and C4 shows the decimal representation of the specified binary numbers in cells B3 and B4, cells C3 and C4 are not needed. They are only shown for clarification.

Explaining formula

Step 1 - Convert binary number to decimal the system

The BIN2DEC function converts a binary number to the decimal number system.

Function syntax: BIN2DEC(number)

BIN2DEC(B3)

becomes

BIN2DEC("00000101")

and returns 5

Step 2 - Perform bitwise XOR operation

The BITOR function performs a bitwise 'OR' of two numbers.

Function syntax: BITOR(number1, number2)

BITOR(BIN2DEC(B3),BIN2DEC(C3))

becomes

BITOR(5,9)

and returns 13

Step 3 - Convert result to binary

The DEC2BIN function converts a decimal number to a binary number.

Function syntax: DEC2BIN(number, [places])

DEC2BIN(BITOR(BIN2DEC(B3),BIN2DEC(C3)),8)

beomes

DEC2BIN(13)

and returns

"00001101".

Back to top

Back to top

Useful resources

BITOR function - Microsoft support
Bitwise OR - wikipedia

7. How to use the BITRSHIFT function

What is the BITRSHIFT function?

The BITRSHIFT function calculates a number based on the decimal system whose binary representation is shifted right by a given number of bits.

What is the binary system?

The binary system is a positional numeral system that uses only two digits: 0 and 1. The binary system is important in our society, many devices like computers, digital cameras, mobile phones and modern cars use binary code to store, process and communicate data. The binary numeral system makes it easy to store and transmit data using binary digits or bits.

The following table shows decimal numbers from 0 to 11 and the binary equivalent:

Decimal	Binary
0	0000
1	0001
2	0010
3	0011
4	0100
5	0101
6	0110
7	0111
8	1000
9	1001
10	1010
11	1011

What are bits?

A bit is a binary digit meaning the single digit in a binary number. Binary numbers are often split into four bits, four bits represents decimal numbers from 0 to 15 or hexadecimal numbers from 0 to F. For example, binary number 0010 has four bits.

What is bits shifted right?

Each bit is moved one position to the right, the easiest way to do this is to add a zero on the left side of the binary number and remove the last digit on the right side to shift bits one position.

Why shift bits right?

Bits shifted left can be used for performing arithmetic operations, manipulating bits, or encoding data. The BITRSHIFT function works only for positive numbers in the decimal system, there is no risk of changing the significant bit when shifting right.

What is the significant bit?

The significant bit or sign bit indicates whether a binary number is positive or negative, if the bit is 0 the number is positive, if the bit is 1, the number is negative.

What is a magnitude bit?

The remaining bits are magnitude bits which represents the absolute value of the number. An absolute number is a number without the sign.

 

1. BITRSHIFT Function Syntax

BITRSHIFT(number, shift_amount)

2. BITRSHIFT Function Arguments

number	Required. The number you want to shift. Must be 0 (zero) or greater than 0 (zero).
shift_amount	Required. How many zeros you want to add to the right side. This number can be negative as well,

3. BITRSHIFT function example

The image above demonstrates a formula in cell D3 that shifts bits right based on a decimal number specified in cell B3, the number of positions shifted right is specified in cell C3.

Formula in cell D3:

Example 1,

Cell B3 contains decimal number 5 which is 0101 in the binary system. Add a 0 (zero) to the left side and remove the last bit on the right side and you get 0010 binary which is number 2 in the decimal system.

Example 2,

Cell B8 contains decimal number 41 which is 0010 1001 in the binary system, cell C8 contains 2 which means that the binary digits are shifted right by two positions. Add two 0's (zeros) to the left side and remove two bits on the right side and you get 0000 1010 in binary which is number 10 in the decimal system.

Back to top

4. How is the BITRSHIFT function calculated in detail?

The table below shows how bits are shifted right using the BITRSHIFT function by one position.

Bit position	3	2	1	0
Binary value 5	0	1	0	1
BITRSHIFT result	0	0	1	0

To shift bits one position to the right add a 0 (zero) to the left side of the binary number and remove the last bit on the right side.

Example 2, to shift bits two steps right add the same amount of 0's (zeros) to the left side of the binary number, in this example two 0's (zeros).

Decimal number 5 is 0101 in the binary system, shifting bits 2 positions results in this binary number: 0001 which is equal to decimal number 1.

Back to top

5. BITRSHIFT function not working

The BITRSHIFT function returns a #NUM! error if

• argument number is 2^48 = 2.81475E+14 or larger. See row 3 in the image above.
• argument number is negative.
• argument number is smaller than 0 (zero).
• argument shift_amount is larger than 53

The BITRSHIFT function returns a #VALUE! error if either of the argument is a letter. See row 5 in the image above.

The BITRSHIFT function seems to work with boolean values TRUE and FALSE. See row 6 in the image above.

Back to top

6. How to shift bits with binary numbers?

The BITRSHIFT function needs a decimal number to be able to shift the binary representation right and the return the decimal representation of the shifted bits.

The following formula lets you shift binary numbers based on a given number of shifts, the result is also a binary number.

Formula in cell D3:

The formula is limited by the BIN2DEC and DEC2BIN functions, they can only handle decimal numbers from -512 to 511.

The following formula works for larger numbers:

However, this may create a new problem with the sign bit that shows if a binary number is positive or negative.

Explaining formula

Step 1 - Convert binary number to decimal the system

The BIN2DEC function converts a binary number to the decimal number system.

Function syntax: BIN2DEC(number)

BIN2DEC(B3)

becomes

BIN2DEC("00000101")

and returns 5

Step 2 - Shift bits to the right

The BITRSHIFT function calculates the number where the binary equivalent is shifted right by a specified number of bits and then converted back to a number.

Function syntax: BITRSHIFT(number, shift_amount)

BITRSHIFT(BIN2DEC(B3),C3)

becomes

BITRSHIFT(5,1)

and returns 2

Step 3 - Convert result to binary

The DEC2BIN function converts a decimal number to a binary number.

Function syntax: DEC2BIN(number, [places])

DEC2BIN(BITRSHIFT(BIN2DEC(B3),C3))

becomes

DEC2BIN(2)

and returns

"0010".

Back to top

Useful resources

BITRSHIFT function - Microsoft support
Bit shifts - Wikipedia

8. How to use the BITXOR function

What is the BITXOR function?

The BITXOR function calculates a decimal number that is a result of a bitwise comparison "XOR" of two decimal numbers, XOR stands for Exclusive OR.

What is a decimal number?

The decimal system is a positional numeral system that uses 10 as the base, it requires 10 different numerals: 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. The dot or the decimal point represents decimal fractions which are not whole numbers.

The decimal number 520 has three positions, each with a different weight. It starts with 10^0 on the right and increases by one power on each additional position to the left.

520 = (5*10^2)+(2*10^1)+(0*10^0)

520 = 500 + 20 + 0

What is a bit?

The binary system is a positional numeral system that uses only two digits: 0 and 1. The binary system is important in our society, many devices like computers, digital cameras, mobile phones and modern cars use binary code to store, process and communicate data. The binary numeral system makes it easy to store and transmit data using binary digits or bits.

The following table shows decimal numbers from 0 to 11 and the binary equivalent:

Decimal	Binary
0	0000
1	0001
2	0010
3	0011
4	0100
5	0101
6	0110
7	0111
8	1000
9	1001
10	1010
11	1011

What is bitwise?

Bitwise operations are performed on the binary representation of numbers, where each bit has a value of either 0 or 1. Some common bitwise operations are AND, OR, XOR, NOT and SHIFT. They can be used for masking, toggling, swapping, testing or arithmetic. This article demonstrates XOR operations.

What is an XOR operation?

The BITXOR function performs XOR logic bit by bit on the numbers based on their binary representation. XOR is an abbreviation for "Exclusive OR" meaning if both digits at each position are not equal, 1 is returned for that position. If they are equal 0 (zero) is returned.

The following operations show how XOR logic work:

0+0=0
1+0=1
0+1=1
1+1=0

Example, the table below shows bitwise XOR logic between two random binary numbers.

Bit position	3	2	1	0
Binary value 1	1	0	0	1
Binary value 2	0	1	0	1
XOR result	1	1	0	0

 

1. BITXOR Function Syntax

BITXOR(number1, number2)

2. BITXOR Function Arguments

number1	Required. A number greater than 0 (zero).
number2	Required. A number greater than 0 (zero).

3. BITXOR Function example

The image above demonstrates a formula in cell D3 that performs exclusive OR between two decimal numbers specified in cells B3 and B4. The decimal numbers are automatically converted in to binary digits and then the BITXOR function performs an exclusive OR operation. Lastly, the binary result is then converted back in to a decimal number.

Formula in cell D3:

The image above shows numbers  5 and 9 in cells B3 and B4 respectively. The BITXOR function converts the decimal numbers to binary numbers 0000 0101 and 0000 1001, the exclusive OR is 0000 1100 which represents 12 in the decimal system shown in cell D3.

Back to top

4. How is the BITXOR function calculated in detail?

Here are the steps to perform bitwise XOR logic:

1. Convert both decimal numbers to binary.
2. Perform bitwise XOR logic, here are the rules:
   0+0=0
   1+0=1
   0+1=1
   1+1=0
3. Convert binary output back to decimal again.

Example

5 is 00000101 binary and 9 is 00001001. See picture below on how to do a bitwise "XOR".

Back to top

5. BITXOR function not working

The BITXOR function returns a #NUM! error if

• argument number is 2^48 = 2.81475E+14 or larger. See row 4 in the image above.
• argument number is negative. See row 3 in the image above.

The BITOR function returns a #VALUE! error if the argument is a letter. See row 5 in the image above.

The BITAND function seems to work with boolean values TRUE and FALSE. See row 6 in the image above.

Back to top

6. How to perform bitwise XOR operations between binary numbers?

The following formula lets you perform bitwise XOR logic based on binary numbers, the result is also a binary number.

Formula:

Cells C3 and C4 shows the decimal representation of the specified binary numbers in cells B3 and B4, cells C3 and C4 are not needed. They are only shown for clarification.

Explaining formula

Step 1 - Convert binary number to decimal the system

The BIN2DEC function converts a binary number to the decimal number system.

Function syntax: BIN2DEC(number)

BIN2DEC(B3)

becomes

BIN2DEC("00000101")

and returns 5

Step 2 - Perform bitwise OR operation

The BITXOR function calculates a decimal number that is a result of a bitwise comparison "XOR" of two numbers.

Function syntax: BITXOR(number1, number2)

BITXOR(BIN2DEC(B3),BIN2DEC(C3))

becomes

BITXOR(5,9)

and returns 12

Step 3 - Convert result to binary

The DEC2BIN function converts a decimal number to a binary number.

Function syntax: DEC2BIN(number, [places])

DEC2BIN(BITXOR(BIN2DEC(B3),BIN2DEC(C3)),8)

beomes

DEC2BIN(12)

and returns

"00001100".

Back to top

Useful resources

BITXOR function - Microsoft support
Bitwise XOR - wikipedia

Back to top

9. How to use the COMPLEX function

What is the COMPLEX function?

The COMPLEX function returns a complex number in the general form (also known as the rectangular form) based on a real and imaginary number.

What is a complex number?

A complex number can be written in this form x+yi, it contains a real and imaginary part. Complex numbers extend the real numbers by allowing solutions to equations that have no real solutions.

Complex numbers can be represented as points or vectors on a plane called the complex plane, where the horizontal axis is the real axis and the vertical axis is the imaginary axis, see the image above. The complex plane allows a geometric interpretation of complex numbers and their operations.

1. COMPLEX Function Syntax

COMPLEX(real_num, i_num, [suffix])

Back to top

2. COMPLEX Function Arguments

real_num	Required. The real coefficient of the complex number.
i_num	Required. The imaginary coefficient of the complex number.
[suffix]	Optional. This argument lets you choose the suffix of the imaginary component. The default value is "i".

The letter j is used in electrical engineering to distinguish between the imaginary value and the electric current.

Back to top

3. COMPLEX Function example

The COMPLEX function is useful when you want to create a complex number with given real and imaginary coefficients. A complex number is a number that has two parts: a real part and an imaginary part. The real part is a number that you are familiar with, such as 2, -5, or 0.5. The imaginary part is a number that is multiplied by a special symbol called i, which stands for the square root of -1.

The COMPLEX function converts real and imaginary coefficients into a complex number of the form x + yi or x + yj, where x represents the real part and y represents the imaginary part.

The COMPLEX function returns a text result, not a numeric result. You can not perform arithmetic operations on the result directly. However, Excel has other functions that work with complex numbers, check out the Engineering category for more imaginary functions.

Formula in cell E3:

Cell B3 contains the real coefficient of the complex number, x in the diagram. Cell C3 contains the imaginary coefficient of the complex number, y in the diagram shown above.

Cell E3 calculates the complex number based on the real and imaginary coefficients specified in cells B3 and C3 respectively.

3.1 Explaining formula

Step 1 - Populate arguments

COMPLEX(real_num, i_num, [suffix])

becomes

COMPLEX(B3,C3)

Step 2 - Evaluate COMPLEX function

COMPLEX(B3,C3)

becomes

COMPLEX(2,3)

and returns

"2+3i".

The COMPLEX function returns complex numbers in general form.

3.2 What is the general form of complex numbers?

The general form of complex numbers is a + bi, where a and b are real numbers and i is the imaginary unit that satisfies i^2 = -1. The real part of a complex number is a, and the imaginary part is b.

3.3 Describe different forms of complex numbers?

Form	Description
General or rectangular	Z = a +bi. Use this form when you want to perform arithmetic operations such as addition, subtraction, multiplication, and division of complex numbers. It also shows the real and imaginary parts of a complex number clearly.
Polar	Z = r(cos θ + i sin θ) This form is useful for finding the roots, powers, and logarithms of complex numbers. It also shows the geometric interpretation of a complex number as a point on the complex plane with a certain distance and direction from the origin.
Exponential	Z = re^(iθ) Use this form when you want to simplify calculations involving trigonometric functions and exponential functions of complex numbers. It also shows the connection between complex numbers and periodic phenomena such as waves and oscillations.
Standard basis form	Example, 2-3i = 2*1 + (-3)*i This form is useful for performing linear algebra operations such as scalar multiplication, vector addition, dot product, and cross product of complex numbers. It also shows the algebraic structure of complex numbers as a two-dimensional vector space.

The general form and the polar form of complex numbers are equivalent and can be converted from one to another using trigonometry and algebra.

3.4 Why is i equal to the square root of -1?

The symbol i is defined as the square root of -1 because there is no real number that satisfies this equation. If we try to find a real number x such that x^2 = -1, we get a contradiction.

For example, if x is positive, then x^2 is also positive, so it cannot be equal to -1.
If x is negative, then x^2 is also positive, for the same reason.
If x is zero, then x^2 is also zero, which is not equal to -1. So there is no real solution to x^2 = -1.

However, mathematicians wanted to extend the real numbers to include a solution to this equation, so they invented a new symbol i (iota) and defined it as the square root of -1. This means that i^2 = -1 by definition. This also means that i is not a real number, but an imaginary number.

We can create complex numbers, by using i, which are numbers that have both a real part and an imaginary part.  Complex numbers are useful because they allow us to solve equations that have no real solutions, such as x^2 + 1 = 0. Using i, we can find two solutions: x = i and x = -i. This is because i^2 = -1 and (-i)^2 = -1.

Back to top

4. COMPLEX Function error

The COMPLEX function returns #VALUE error if the two first arguments are not numbers.

The COMPLEX function also returns a #VALUE if the suffix is not "i", "j" or omitted.

Back to top

Back to top

Useful resources

COMPLEX function - Microsoft
Intro to complex numbers
An Introduction to Complex Numbers

10. How to use the CONVERT function

The CONVERT function converts a number from one measurement system to another. It lets you perform conversions in these categories: weight and mass, distances, time, pressure, force, energy, magnetism, temperature, volume, area, speed, and prefixes.

1. CONVERT Function Syntax

CONVERT(number, from_unit, to_unit)

2. CONVERT Function Arguments

number	Required. The number you want to convert from.
from_unit	Required. The unit you want to convert from.
to_unit	Required. The unit you want to convert to.

Weight and mass	From_unit or to_unit
Gram	"g"
Slug	"sg"
Pound mass (avoirdupois)	"lbm"
U (atomic mass unit)	"u"
Ounce mass (avoirdupois)	"ozm"
Grain	"grain"
U.S. (short) hundredweight	"cwt" or "shweight"
Imperial hundredweight	"uk_cwt" or "lcwt" ("hweight")
Stone	"stone"
Ton	"ton"
Imperial ton	"uk_ton" or "LTON" ("brton")

Distance	From_unit or to_unit
Meter	"m"
Statute mile	"mi"
Nautical mile	"Nmi"
Inch	"in"
Foot	"ft"
Yard	"yd"
Angstrom	"ang"
Ell	"ell"
Light-year	"ly"
Parsec	"parsec" or "pc"
Pica (1/72 inch)	"Picapt" or "Pica"
Pica (1/6 inch)	"pica"
U.S survey mile (statute mile)	"survey_mi"

Time	From_unit or to_unit
Year	"yr"
Day	"day" or "d"
Hour	"hr"
Minute	"mn" or "min"
Second	"sec" or "s"

Pressure	From_unit or to_unit
Pascal	"Pa" (or "p")
Atmosphere	"atm" (or "at")
mm of Mercury	"mmHg"
PSI	"psi"
Torr	"Torr"

Force	From_unit or to_unit
Newton	"N"
Dyne	"dyn" (or "dy")
Pound force	"lbf"
Pond	"pond"

Energy	From_unit or to_unit
Joule	"J"
Erg	"e"
Thermodynamic calorie	"c"
IT calorie	"cal"
Electron volt	"eV" (or "ev")
Horsepower-hour	"HPh" (or "hh")
Watt-hour	"Wh" (or "wh")
Foot-pound	"flb"
BTU	"BTU" (or "btu")

Power	From_unit or to_unit
Horsepower	"HP" (or "h")
Pferdestärke	"PS"
Watt	"W" (or "w")

Magnetism	From_unit or to_unit
Tesla	"T"
Gauss	"ga"

Temperature	From_unit or to_unit
Degree Celsius	"C" (or "cel")
Degree Fahrenheit	"F" (or "fah")
Kelvin	"K" (or "kel")
Degrees Rankine	"Rank"
Degrees Réaumur	"Reau"

Volume (or l iquid measure )	From_unit or to_unit
Teaspoon	"tsp"
Modern teaspoon	"tspm"
Tablespoon	"tbs"
Fluid ounce	"oz"
Cup	"cup"
U.S. pint	"pt" (or "us_pt")
U.K. pint	"uk_pt"
Quart	"qt"
Imperial quart (U.K.)	"uk_qt"
Gallon	"gal"
Imperial gallon (U.K.)	"uk_gal"
Liter	"l" or "L" ("lt")
Cubic angstrom	"ang3" or "ang^3"
U.S. oil barrel	"barrel"
U.S. bushel	"bushel"
Cubic feet	"ft3" or "ft^3"
Cubic inch	"in3" or "in^3"
Cubic light-year	"ly3" or "ly^3"
Cubic meter	"m3" or "m^3"
Cubic Mile	"mi3" or "mi^3"
Cubic yard	"yd3" or "yd^3"
Cubic nautical mile	"Nmi3" or "Nmi^3"
Cubic Pica	"Picapt3", "Picapt^3", "Pica3" or "Pica^3"
Gross Registered Ton	"GRT" ("regton")
Measurement ton (freight ton)	"MTON"

Area	From_unit or to_unit
International acre	"uk_acre"
U.S. survey/statute acre	"us_acre"
Square angstrom	"ang2" or “ang^2"
Are	"ar"
Square feet	"ft2" or "ft^2"
Hectare	"ha"
Square inches	"in2" or "in^2"
Square light-year	"ly2" or "ly^2"
Square meters	"m2" or "m^2"
Morgen	"Morgen"
Square miles	"mi2" or "mi^2"
Square nautical miles	"Nmi2" or "Nmi^2"
Square Pica	"Picapt2", "Pica2", "Pica^2" or "Picapt^2"
Square yards	"yd2" or "yd^2"

Information	From_unit or to_unit
Bit	"bit"
Byte	"byte"

Speed	From_unit or to_unit
Admiralty knot	"admkn"
Knot	"kn"
Meters per hour	"m/h" or "m/hr"
Meters per second	"m/s" or "m/sec"
Miles per hour	"mph"

Prefix	Multiplier	Abbreviation
yotta	1E+24	"Y"
zetta	1E+21	"Z"
exa	1E+18	"E"
peta	1E+15	"P"
tera	1000000000000	"T"
giga	1000000000	"G"
mega	1000000	"M"
kilo	1000	"k"
hecto	100	"h"
dekao	10	"da" or "e"
deci	0.1	"d"
centi	0.01	"c"
milli	0.001	"m"
micro	0.000001	"u"
nano	0.000000001	"n"
pico	0.000000000001	"p"
femto	0.000000000000001	"f"
atto	1E-18	"a"
zepto	1E-21	"z"
yocto	1E-24	"y"

Binary Prefix	Prefix Value	Abbreviation	Derived from
yobi	2^80 = 1 208 925 819 614 629 174 706 176	"Yi"	yotta
zebi	2^70 = 1 180 591 620 717 411 303 424	"Zi"	zetta
exbi	2^60 = 1 152 921 504 606 846 976	"Ei"	exa
pebi	2^50 = 1 125 899 906 842 624	"Pi"	peta
tebi	2^40 = 1 099 511 627 776	"Ti"	tera
gibi	2^30 = 1 073 741 824	"Gi"	giga
mebi	2^20 = 1 048 576	"Mi"	mega
kibi	2^10 = 1024	"ki"	kilo

3. CONVERT Function example

Formula in cell E3:

The CONVERT function converts from Celsius to Fahrenheit in cell E3.

Back to top

4. CONVERT Function error

The CONVERT function returns a #N/A error if the unit doesn't exist.

Unit names and prefixes are case-sensitive.

Can the CONVERT Function convert from decimal to binary/hexadecimal/octal?
No, there are dedicated functions for these types of conversions.
DEC2BIN function | DEC2HEX function | DEC2OCT function

Can the CONVERT Function convert from binary to decimal/hexadecimal/octal?
No, there are dedicated functions for these types of conversions.
BIN2DEC function | BIN2HEX function | BIN2OCT function

Can the CONVERT Function convert from hexadecimal to decimal/binary/octal?
No, there are dedicated functions for these types of conversions.
HEX2BIN function | HEX2DEC function | HEX2OCT function

Back to top

5. Unit conversion tool

I built a unit conversion tool, below is a link the file. It lets you quickly convert a number from a given unit to another given unit using drop down lists. It is a quick and easy way for unit conversions in Excel.

5.1 Here is how the unit conversion tool works

The image above shows the unit conversion tool, it has three cells B3,C3, and D3 containing different drop down lists. The drop down lists in cells C3 and D3 are populated based on the selected category in cell B3.

The drop down list in cell B3 contains these categories: Area, Distance, Energy, Force, Information, Power, Pressure, Speed, Temperature, Time, Volume, Weight and mass.

Cell B6 lets you specify the number you want to convert, cell C3 specifies the unit and cell D3 specifies which unit you want to convert to. The result is calculated in cell C6, the image above demonstrates how to convert from 1 feet to meter. The result is 0.3048 meter. The chosen category in cell B3 is "Distance", cell C3 contains "ft" and cell D3 contains "m".

5.2 Here is how I built it

I copied and pasted all arguments to one table. I copied the header name for each category to column H.

I removed all double quotes and removed everything after "or".  For example, one unit was specified like this: "day" or "d". After removing double quotes and "or ...": day

I then created a formula that extracts unique distinct category names from cell range H3:H128.

Excel 365 formula in cell N3:

The formula also sorts the category names from A to Z, the result is a dynamic array that spills its values to cells below automatically.

I inserted a drop down list to cell B3.

1. Select cell B3.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on the "Data Validation" button.
   
4. Press with left mouse button on the "Data Validation...", a dialog box appears.
   
5. Select "List" below "Allow:".
6. Select cell N3 below "Source:", make sure it ends with a hashtag. This makes sure that every value from the Excel 365 dynamic array is included.
7. Press with left mouse button on the "OK" button.

I then created a formula that extracts units based on the selected value in cell B3.

Excel 365 dynamic array formula in cell O3:

This formula extracts the corresponding units to the selected category and sorts them from A to Z.

I then inserted drop down lists to cells C3 and D3, see the steps above on how to configure these drop down lists. The source cells are both O3#.

The last formula is the CONVERT function, it uses the specified values in cells B6, C3, and D3 to perform the conversion.

Formula in cell C6:

Back to top

Back to top

Useful resources

CONVERT Function - Microsoft
Unit converter
Unit conversion

Back to top

More than 1300 Excel formulas

Excel categories

Leave a Comment