BITAND in Google Sheets: Check Required Flags and Shared Bits
Inspect numeric masks with Google Sheets BITAND, compare any and all required flags and decode binary text before testing its bits.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: BITAND(value1,value2) returns the decimal value of bits present in both inputs. For a flag test, compare that numeric result with zero or with the required mask, depending on whether you need any shared flag or every required flag.
Availability and version history
- Introduced: first introduction date is not established by the reviewed official history; no desktop release number is inferred.
- Availability: documented in the rolling Google Sheets service; these examples use the web worksheet workflow.
- Checked: October 14, 2026; official BITAND reference. Documentation and independent models, with no native Sheets execution or wider client certification.
Syntax and decimal inputs
=BITAND(value1,value2)
Both arguments are decimal representations of numbers. Google’s truth table retains a bit only for the input pair 1 and 1; the other three bit pairs produce zero. This is a position-by-position operation, not the logical AND function applied to general conditions.
The dedicated reference does not give a complete input-size ceiling, fractional-number rule or error-code table. The examples therefore use small nonnegative integers. Do not copy limits or error behavior from a similarly named function in another spreadsheet.
If the original data is binary text, convert it with BIN2DEC first. The literal decimal number 10110 and the binary representation “10110” do not describe the same quantity.
Example 1: inspect report-output options
Define a fictional output configuration in D1:E6:
| D, option | E, weight |
|---|---|
| CSV file | 1 |
| PDF report | 2 |
| Charts | 4 |
| Archive copy | 8 |
| Metadata | 16 |
Enter the selected mask 22 in A2. In F2 enter and fill through F6:
=BITAND($A$2,E2)
Expected results are 0, 2, 4, 0 and 16. Binary 22 is 10110, or 16+4+2. PDF, charts and metadata are selected; CSV and archive are absent.
In G2 enter =F2>0 and fill down. The Boolean results are FALSE, TRUE, TRUE, FALSE and TRUE. The extraction formula and the decision formula remain separate, which makes the report easier to inspect.
Change A2 to 9. The extracted weights become 1, 0, 0, 8 and 0. That configuration selects CSV and archive only. Restore A2 to 22 before the next example.
Example 2: any required output versus all outputs
Put 6 and 10 in I2:I3 as requirement masks. Mask 6 requires PDF plus charts; mask 10 requires PDF plus archive.
In J2 enter =BITAND($A$2,I2) and fill to J3. The results are 6 and 2. Then compare:
| Requirement | Any present, J>0 |
All present, J=I |
|---|---|---|
| 6 | TRUE | TRUE |
| 10 | TRUE | FALSE |
The second requirement shares the PDF bit but lacks archive. A formula that checks only nonzero would answer an any-output question and could be too permissive for an all-output requirement.
A zero requirement is a separate policy choice. =BITAND(22,0)=0 is TRUE, while =BITAND(22,0)>0 is FALSE. There are no required bits to fail, but no shared selected bit either. Decide which interpretation belongs in the report before using the test as a filter.
Advanced example: inspect imported binary strings
Format L2:M2 as Plain text and enter 10110 and 10011. In N2 use:
=BITAND(BIN2DEC(L2),BIN2DEC(M2))
The decimal inputs become 22 and 19. Their shared bits are 10010, or 18. Independently, 10110 and 10011 both contain the 16 and 2 positions, so the shared total is 16+2.
For a readable label, =DEC2BIN(N2,5) returns 10010. DEC2BIN is a representation step; the actual BITAND result is decimal 18.
Google explicitly demonstrates this conversion-first pattern. Keep the imported text and the decoded number in separate columns when the original digit width matters. A numeric conversion cannot preserve the distinction between a leading-zero code and its shorter equivalent unless you also keep that width.
Interpretation and remaining boundaries
=BITAND(22,22) returns 22 and =BITAND(22,0) returns zero in the small-integer model. Neither result establishes that a configuration is complete or valid for another application. The bit-to-option table supplies that meaning.
Use BITOR to combine selected bits and BITXOR to identify differing positions. Those operations answer different questions from shared-bit extraction.
The examples make no claim about imported errors, negative masks, decimal fractions or maximum native widths. A successful bitwise calculation is also not a substitute for checking whether the source data follows the expected schema.
References and verification
Examples use fictional data, English function names and comma separators. Expected outputs are independent mathematical calculations or explicitly documented rules, not native Sheets captures. Check the spreadsheet locale when reproducing punctuation-sensitive formulas.