MOD returns the remainder after division. Use it for leftover quantities, even or odd checks, and repeating row patterns.
MOD function syntax
=MOD(dividend, divisor)- dividend: number being divided.
- divisor: number to divide by; use a nonzero value.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Number | Divisor |
|---|---|
| 17 | 5 |
| -7 | 3 |
| 7 | -3 |
| -7 | -3 |
| 8 | 2 |
| 130 | 60 |
Find the remainder
Enter the formula in A10. Seventeen contains three complete groups of five, leaving two. An exact division returns zero.
=MOD(A2,B2)Result: 2.
Use negative numbers
The nonzero remainder follows the divisor’s sign. Here, negative seven divided using a positive divisor of three gives a remainder of two.
=MOD(A3,B3)Result: 2.
Check whether an integer is odd
A remainder of one identifies an odd integer when the divisor is two. Even integers return zero. Use ISEVEN or ISODD when you prefer a logical result.
=MOD(7,2)Result: 1.
Flag every third data row
This example treats A2 as the first data row. A4 is the third data row, so it returns Yes. Use the same offset with a different divisor for another interval.
=IF(MOD(ROW(A4)-ROW($A$2)+1,3)=0,"Yes","No")Result: Yes.
Find leftover minutes
The total of 130 minutes contains two full hours and ten leftover minutes. MOD returns only those ten minutes.
=MOD(A7,60)Result: 10.
Cycle through three numbered buckets
SEQUENCE supplies positions one through six. Subtracting one shifts them to zero-based positions; adding one after MOD gives bucket numbers one through three. Leave six output cells empty.
=ARRAYFORMULA(MOD(SEQUENCE(6)-1,3)+1)Result: 1; 2; 3; 1; 2; 3.
Round decimal remainders
Google documents possible floating-point approximation for non-integer inputs. Round to the precision appropriate for your data when comparing decimal results.
=ROUND(MOD(0.3,0.1),10)Result: 0.
Other Google Sheets articles you may also like