Logical & Conditional
Check If a Cell Contains a Whole Integer vs. Decimal (ISNUMBER, INT & MOD)
Validate discrete carton quantities, headcount, and serial IDs using AND(ISNUMBER(B2), B2 = INT(B2))—rejecting fractional decimals (4.25), text strings ("12"), and blank cells cleanly.
Catch fractional carton orders and text-formatted numbers before ERP import!
Warehouse carton quantities, headcount plans, and patient age years must be whole numbers—a stray =B2/3 formula producing 14.333 cartons or a text string "15" pasted from a web form will crash strict integer database columns during CSV upload!
Testing B2 = INT(B2) alone has two subtle traps: an empty cell evaluates 0 = INT(0) -> TRUE, and a text string makes INT("abc") throw #VALUE!! Combining AND(NOT(ISBLANK(B2)), ISNUMBER(B2), B2 = INT(B2)) (or MOD(B2, 1) = 0) guarantees TRUE strictly for genuine numeric integers—positive, zero, or negative (-12 = INT(-12) is TRUE!)—while returning FALSE for decimals (14.5), text ("15"), and blanks!
| Cell Input Value | ISNUMBER(B2) | Whole-Integer Test AND(NOT(ISBLANK), ISNUMBER, B2=INT(B2)) |
|---|---|---|
| 48 (Whole Carton Count) | TRUE | TRUE (Valid Whole Integer) |
| -12 (Signed Adjustment) | TRUE | TRUE (Valid Negative Integer) |
| 14.25 (Fractional Split) or "48" (Text) | TRUE / FALSE | FALSE (Rejected Non-Integer) |
Strict Whole-Integer Validation Formula (Positive, Zero, or Negative)
Rejects blanks, text strings, and non-zero fractional remainders without throwing #VALUE!.
=IF(OR(ISBLANK(B2), NOT(ISNUMBER(B2))), FALSE, B2 = INT(B2))Alternative Remainder Check & Fractional Split Audit Using MOD(cell, 1) = 0
Tests MOD(B4, 1) = 0 (which evaluates to TRUE for negative integers like -12 as well as 0 and +48) and labels non-integer order quantities.
=IF(OR(ISBLANK(B4), NOT(ISNUMBER(B4))), "Non-Numeric Entry", IF(MOD(B4, 1) = 0, "Valid Whole Integer", "Fractional Decimal Reject"))Try the example
Use the original values or change them to test your understanding.