Excel MOD Function: remainders, every Nth row, odd and even

MOD gives the remainder after dividing. 7 divided by 2 is 3 with 1 left over, so =MOD(7,2) is 1.

The formula

=MOD(number, divisor)

On its own that sounds dull. The uses are what make it popular.

1. Odd or even

=IF(MOD(A2,2)=0,"Even","Odd")

2. Shade every 3rd row

Select the data › Home › Conditional Formatting › New Rule › Use a formula:

=MOD(ROW(),3)=0

Pick a fill. Change 3 to any number. For simple banding, see alternate row colour.

3. Minutes into hours and minutes

135 minutes in A2:

=INT(A2/60)&" h "&MOD(A2,60)&" min"

Result: 2 h 15 min.

4. Repeat a cycle (1,2,3,1,2,3…)

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

Handy for assigning people to 3 shifts or 3 groups in turn.

5. Time that crosses midnight

Start 22:00, end 06:00: =MOD(B2-A2,1) gives 8 hours instead of a negative number. More in time difference.

Watch out

Dividing by 0 gives #DIV/0!. With negative numbers the result takes the sign of the divisor: =MOD(-7,2) is 1, not -1.