# Engineering functions – A to C

### Table of Contents

- How to use the BIN2DEC function
- How to use the BIN2HEX function
- How to use the BIN2OCT function
- How to use the BITAND function
- How to use the BITLSHIFT function
- How to use the BITOR function
- How to use the BITRSHIFT function
- How to use the BITXOR function
- How to use the COMPLEX function
- How to use the CONVERT function

## 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

### Table of Contents

### 1. BIN2DEC Function Syntax

BIN2DEC(*number*)

### 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

### 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.

### 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

### 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.

### 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".

### 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".

### 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 **b**inary dig**it. **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.

### Table of Contents

### 1. BIN2HEX function Syntax

BIN2HEX(*number*,*[places]*)

### 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

### 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.

### 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 |

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

### 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.

### 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 **b**inary dig**it. **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.

### Table of Contents

### 1. BIN2OCT function Syntax

BIN2OCT(*number*,*[places]*)

### 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

### 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.

### 4. How is the BIN2OCT function calculated in detail?

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

- Divide the binary number into groups of three bits starting from the rightmost bit.
- Add leading zeros to make it a full group if the leftmost group contains less than three bits.
- Find the corresponding octal digit for each group using this table:
- Write the octal digits in the same order as the binary groups.

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 |

### 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.

### 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).

### Table of Contents

### 1. BITAND function Syntax

BITAND(*number1*, *number2*)

### 2. BITAND function Arguments

number1 |
Required. The first number. |

number2 |
Required. The second number. |

### 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.

### 4. How is the BITAND function calculated in detail?

Here are the steps to perform bitwise AND logic:

- Convert both decimal numbers to binary.
- Perform bitwise AND logic.
- 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.

### 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".

### 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 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 **b**inary dig**it **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.

### Table of Contents

## 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.

## 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.

## 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.

## 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".

### 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 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.

### Table of Contents

## 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.

## 4. How is the BITOR function calculated in detail?

Here are the steps to perform bitwise OR logic:

- Convert both decimal numbers to binary.
- Perform bitwise OR logic.
- 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.

## 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.

## 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".

### 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 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 **b**inary dig**it **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.

### Table of Contents

### 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.

### 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.

### 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.

### 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".

### 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.

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

520 = 500 + 20 + 0

##### What is a bit?

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 |

### Table of Contents

### 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.

### 4. How is the BITXOR function calculated in detail?

Here are the steps to perform bitwise XOR logic:

- Convert both decimal numbers to binary.
- Perform bitwise XOR logic, here are the rules:

0+0=0

1+0=1

0+1=1

1+1=0 - 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".

### 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.

### 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".

### Useful resources

BITXOR function - Microsoft support

Bitwise XOR - wikipedia

## 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.

### Table of Contents

### 1. COMPLEX Function Syntax

COMPLEX(*real_num*, *i_num*, *[suffix]*)

### 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.

### 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.

### 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.

### 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.

### Table of Contents

### 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.

### 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

### 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.

- Select cell B3.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on the "Data Validation" button.

- Press with left mouse button on the "Data Validation...", a dialog box appears.

- Select "List" below "Allow:".
- 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.
- 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:

### Useful resources

CONVERT Function - Microsoft

Unit converter

Unit conversion

### Excel categories

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form