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.

1. Introduction

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 bit-wise?

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

When is bit-wise AND logic useful?

1. Checking if a number is even or odd: To check if a number is even, we can use BITAND with 1. If the result is 0, the number is even. Example 1:
   • 6 (decimal) = 0110 (binary)
   • 1 (decimal) = 0001 (binary)
   • 6 BITAND 1 = 0110 AND 0001 = 0000 (Result is 0, so 6 is even)
   • Example 2:
   • 7 (decimal) = 0111 (binary)
   • 1 (decimal) = 0001 (binary)
   • 7 BITAND 1 = 0111 AND 0001 = 0001 (Result is 1, so 7 is odd)
2. Extracting specific bits from a number: Let's say we want to extract the 3rd and 4th bits from a byte. Example:
   • Original number: 170 (decimal) = 10101010 (binary)
   • Mask: 12 (decimal) = 00001100 (binary)
   • 170 BITAND 12= 10101010 AND 00001100 = 00001000 (8 in decimal)
3. Implementing simple permissions systems: We can use BITAND to check if a user has specific permissions. Example:
   • User permissions: 5 (decimal) = 0101 (binary) (Read and Run)
   • Required permission: 4 (decimal) = 0100 (binary) (Write)
   • 5 BITAND 4 = 0101 AND 0100 = 0100
   • Since the result is non-zero, the user has the required permission.
4. Clearing specific bits: To clear (set to 0) specific bits in a number, we can use BITAND with a mask. Example:
   • Original number: 255 (decimal) = 11111111 (binary)
   • Mask: 247 (decimal) = 11110111 (binary) (to clear the 4th bit)
   • 255 BITAND 247 = 11111111 AND 11110111 = 11110111 (247 in decimal)

2. Syntax

BITAND(number1, number2)

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

Back to top

3. 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. Their binary representation are in cells C8 and C9 respectively.

Formula in cell D3:

The formula in cell D3 returns 1 which is the decimal representation of binary AND-logic of 5 and 9. It performs AND logic for each bit between the two decimal numbers and the binary result is converted into a decimal number which in this case is 1.

The next formula in cell D8 performs a bit-wise AND-logic between decimal numbers 45 and 21 which are located in cells B8 and B9 respectively. The result is shown in cell D8 which contains 5. The binary AND-logic is shown in cells C8 and C9 and the binary result is displayed in cell C10.

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 bit-wise AND logic between two binary numbers, the result is also binary.

Formula in cell D3:

Here is a breakdown of how it works:

• BIN2DEC(B3) and BIN2DEC(C3) convert the binary numbers in cells B3 and C3 to decimal numbers.
• BITAND(BIN2DEC(B3),BIN2DEC(C3)): BITAND performs a bitwise AND operation between the two decimal numbers. This operation compares each bit of the first number to the corresponding bit of the second number. If both bits are 1, the corresponding result bit is set to 1. Otherwise, the corresponding result bit is set to 0.
• DEC2BIN(BITAND(BIN2DEC(B3),BIN2DEC(C3))): DEC2BIN converts the result of the bitwise AND operation back to a binary number.

For example, if B3 contains the binary number 0000 0101 and C3 contains the binary number 0110 0101, the formula would work as follows:

• BIN2DEC(B3) converts 0000 0101 to the decimal number 5.
• BIN2DEC(C3) converts 0110 0101 to the decimal number 101.
• BITAND(5, 101) performs the bitwise AND operation, resulting in the decimal number 5.
• DEC2BIN(5) converts the result back to the binary number 0000 0101.
  The final result of the formula is 0000 0101.

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

'BITAND' function examples

The following article has a formula that contains the BITAND function.

Functions in 'Engineering' category

The BITAND function function is one of 42 functions in the 'Engineering' category.

Excel function categories

Excel categories

Leave a Comment