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**

**Number**The number for which you want to find the remainder.

**Divisor**The number by which you divide

=MOD(Number, Divisor)

MOD will return the rest of the division. **Is that clear? **Let's see when to use it with the following example: You are the manager of an egg company. Every day, you receive eggs, which you pack in egg packs of 6.

So you have 2 important questions to solve

- How many boxes will I fill?
- How many leftover eggs will you have in the last box?

Imagine receiving 8565 eggs (the number), and you will pack them by 6 (the divisor). Answering the first question is very easy. You must divide and keep the integer part (at the end of the article you will see another solution)

=INT(8565/6)

**It's for answering 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 automatically recalculate 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

## Related articles

- TOP 10 Most Useful Statistics Excel Functions for Data Analysis
- Function XOR in Excel
- Round to 10, 25, or 50 cents with Excel
- Rounding to the Nearest Tenth or Hundredth with Excel

Brad R

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