How to Use the MOD Function in Google Sheets
=MOD(dividend, divisor). =MOD(10, 4) returns 2.
What MOD does
When you divide 10 by 4, you get 2 with 2 left over. MOD returns that leftover part. It is the tool for questions such as "is this number divisible by 3?", "which rows are every fifth row?" and "what is the minute part of a total number of minutes?"
MOD syntax
=MOD(dividend, divisor)
dividend: the number to divide, from which the remainder is found.divisor: the number you divide by.
Per Google's help page, MOD also works with non-integers, but floating-point arithmetic can make the result approximate. The page suggests wrapping the result in ROUND if you want the value you expect.
A step-by-step example
Cells A2:B3 hold two numbers and a divisor.
| A | B | |
|---|---|---|
| 1 | Number | Divisor |
| 2 | 17 | 5 |
| 3 | 9 | 3 |
=MOD(A2, B2)
This returns 2. Here is why: 5 goes into 17 three times (15), and 2 is left over.
Check divisibility
A remainder of 0 means the number divides evenly. Combine MOD with IF:
=IF(MOD(A3, 3) = 0, "Yes", "No")
This returns Yes, because 9 divided by 3 leaves no remainder. The same idea can mark every second row, using ISEVEN and ISODD as a shortcut.
Common mistakes
- Swapping the arguments. The number to divide comes first, the divisor second.
- Trusting decimals blindly. Floating-point results can be slightly off, so round them.
- Forgetting that 0 means divisible. Compare the result with 0, not with 1.
Try it yourself
Use the table above (A1:B3). Type your formula into the box, starting with =, then press Check.
Exercise 1: The remainder
Write a formula that returns the remainder when A2 is divided by B2.
Exercise 2: Divisible by 3?
Write a formula that shows Yes if A3 is divisible by 3 and No if it is not.
What to learn next
- ISEVEN and ISODD: test for even and odd numbers.
- ROUNDUP and ROUNDDOWN: always round up or always round down.
- IF: run a test and return one of two results.
Syntax and rules in this guide were checked against Google's MOD help page. See all guides.