Remainders & Cycles with MOD

All versionsMOD

MOD returns the remainder after division — the key to testing odd/even, cycling through a repeating pattern, wrapping values around, and grabbing every Nth item. A small function with a surprising number of uses.

Quick formula: to get the remainder of A2 divided by 3:

=MOD(A2, 3)

MOD(7, 3) is 1 (7 = 2×3 + 1). A remainder of 0 means it divides evenly.

Functions used (tap for the full reference guide):

MOD INT

The example

MOD by 2 flags odd/even; MOD by 3 cycles 0-1-2.

	A	B	C
1	n	MOD(n,2)	Odd/Even
2	7	1	Odd
3	10	0	Even
4	15	1	Odd

The formula

Test even with a 0 remainder:

=IF(MOD(A2, 2)=0, "Even", "Odd") // remainder 0 → divides evenly → Even

How it works

The remainder unlocks several patterns:

MOD(n, d) returns what’s left after dividing n by d. MOD(7,3)=1.
Odd/even: MOD(n, 2)=0 is TRUE for even numbers.
Cycle: MOD(ROW()-1, 3) repeats 0,1,2,0,1,2… — useful for grouping every 3 rows or assigning round-robin.
Wrap: MOD(angle, 360) keeps a value within a range; MOD(time, 1) strips the date from a date-time.

MOD + INT split a number: INT(A2/12) is the whole dozens, MOD(A2, 12) is the leftover units — the basis for converting totals into groups (dozens, hours/minutes, etc.).

Try it: interactive demo

Live demo

Enter a number and a divisor.

Number

Divisor

Remainder:

Variations

Cycle through 1-N

Round-robin assignment:

=MOD(ROW()-2, 3) + 1

Sum every 3rd row

Mask with MOD:

=SUMPRODUCT((MOD(ROW(B2:B13)-ROW(B2),3)=0)*B2:B13)

Strip date from a timestamp

Keep just the time fraction:

=MOD(A2, 1)

Pitfalls & errors

#DIV/0! with a zero divisor. MOD(n, 0) is undefined. Guard the divisor if it could be 0.

Negative numbers follow the divisor’s sign. MOD(-1, 3) is 2 in Excel (not -1) — the result takes the sign of the divisor. Handy, but surprising.

Floating-point quirks. MOD on decimals can show tiny rounding errors; ROUND the result if exactness matters.

Practice workbook

📊

Download the free Remainders & Cycles with MOD practice workbook

Numbers with live MOD odd/even, cycling, every-Nth, and time-strip examples, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

What does the MOD function do in Excel?

MOD returns the remainder after division: =MOD(7, 3) is 1. A remainder of 0 means the number divides evenly, which is how you test for odd/even or multiples.

How do I cycle through a repeating pattern with MOD?

Use MOD on a counter: =MOD(ROW()-2, 3)+1 repeats 1,2,3,1,2,3, ideal for round-robin assignment or grouping every N rows.

Why does MOD give a positive result for a negative number?

In Excel, MOD takes the sign of the divisor, so MOD(-1, 3) is 2. Use this deliberately or adjust if you need a different convention.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: MOD · INT