The MOD function of Excel returns the rest of a division. This function is very useful for
- a logical test
- the conditional formatting
- data validation
Presentation of the MOD function of Excel
The MOD function needs two argument
- The number
- The divisor
MOD will return rest of the division. Not clear? Let's see when to use it with the following exampl.
You are the manager of an egg company. Everyday you receive eggs and you will pack them in egg packs of 6.

So you have 2 important question to solve
- How many box I will fill?
- How many leftover eggs will you have in the last box?
Imagine that you receive 8565 eggs (the number) and you will pack them by 6 (the divisor).
Answering the first question is very easy. You must do a division and keep the integer part (at the end of the article you will see another solution)
=INT(8565/6)

It's for answering to the second question that the MOD function is very useful.
=MOD(8565,6)

Now, if you use a box with 12 spaces, your Excel dashboard will recalculate automatically your model

MOD function in a logical test
You can also use the MOD function in a logical test, like for instance the odd or even numbers.
- Even numbers are 0, 2, 4, 6, 8, 10, .....
- Odd numbers are 1, 3, 5, 7, 9, 11, .....
There is always a step of 2 units between each number. So, a division by 2 will return 0 or 1

And we can convert the formula to a logical test like this
- =MOD(A2,2)=0 for the Even Number
- =MOD(A2,2)<>0 for the Odd Number

Now, you can use your test, either with the IF function, or in a conditional formating rules.
For instance, in this example, we have used exactly the same test as cutom rules and only the Even numbers are colored

QUOTIENT function
For the very first example, we have calculate the number of boxes with a division inside the INT function.
But we could also returned the same result with the QUOTIENT function
=QUOTIENT(B1,B2) = INT(B1/B2) =>1427

17/03/2018 @ 06:36
Hi ,Could you please advise if it is possible to reference a date with a value to be repeated by frequency,( ie 7 days, 30, 90 days,180 days) from that date using Mod & IF syntax nested
Thanks
Brad