MOD vs QUOTIENT: remainder and integer division
Divide 17 by 5 and you get two useful integers: how many times it fits (QUOTIENT → 3) and what's left over (MOD → 2). Together they decompose any division — and MOD's sign convention on negatives trips up everyone arriving from a programming language.
The differences at a glance
| MOD | QUOTIENT | |
|---|---|---|
| 17 and 5 | 2 (remainder) | 3 (how many fit) |
| -17 and 5 | 3 — result takes the DIVISOR's sign (unlike C/JS/Java's -2) | -3 (truncates toward zero) |
| Identity | n = divisor × QUOTIENT + ... not always! Use n - MOD(n,d) = floor-multiple instead | Pairs with MOD only when signs agree |
| Classic idioms | Every-nth-row (MOD(ROW(),n)), cycling indexes, time-of-day from datetime (MOD(A2,1)), overnight shifts | Full boxes/packs from a count, pagination |
| Compatibility | Universal | Universal (both verified in every version of all three apps we test) |
Which should you use?
- MOD — Cycles and leftovers: alternating row bands, wrapping indexes, extracting the time fraction from a datetime, every-nth-row sums. Its divisor-sign rule is exactly what makes MOD(B2-A2,1) handle past-midnight time gaps.
- QUOTIENT — "How many whole X fit": full cartons from an order count, complete weeks from days. INT(a/b) matches it for positives but rounds DOWN for negatives where QUOTIENT truncates toward zero — pick deliberately.
Compatibility (from executed tests)
Both behave identically in every version of Excel, Google Sheets, and LibreOffice we test, including the negative-number conventions. The spreadsheet MOD-takes-the-divisor's-sign rule matches Python — not C, JavaScript, or Java — so ported formulas involving negative inputs deserve a test case.
Example formulas
| Remainder | =MOD(17,5) |
| Whole units | =QUOTIENT(17,5) |
| Time-of-day from a datetime | =MOD(A2,1) |
Full per-version details on each function page: MOD · QUOTIENT.