Excel MOD Function

The Excel MOD Function is probably one of the most confusing to understand, unless you’re a math whizz, which I am not. After all this is why I use Excel. It’s my giant calculator of choice.

Anyhow, just because I find MOD confusing, doesn’t mean it’s not one of the most useful and versatile functions available to us. And to be fair, it’s not a complicated function, but the description of what the MOD function does is as clear as mud, just take a look:

“MOD returns the remainder after a number is divided by a divisor.”

Unless you’ve heard of MOD before, it might as well read; blah, blah, blah, blah.

Excel MOD Function Syntax

=MOD(number, divisor)

Number – the number to be divided

Divisor – the number to divide by

Excel MOD Function Examples

Let’s look at some MOD Function examples and you’ll (hopefully) see it’s actually quite simple:

I’ll explain the way I understand what MOD does. Let’s take the examples above, starting with row 5:

• Row 5: 3 goes into 9, 3 times. There is nothing left, so MOD returns 0. In other words, 9 is divisible by 3. I can calculate the result of MOD like this:
  • 9 - (3 x 3) = 0
• Row 6: 3 goes into 10, 3 times. There is 1 left. In other words, 10 is not divisible by 3. I can calculate the result of MOD like this:
  • 10 - (3 x 3) = 1
• Row 7: 4 goes into 6, 1 time. There are 2 left. In other words, 6 is not divisible by 4. I can calculate the result of MOD like this:
  • 6 - (4 x 1) = 2

Good Things to Know about MOD:

1. The result of MOD will always match sign of the divisor. Compare examples on rows 7:10.
2. Using 2 as the divisor will always return 1 for odd ‘numbers’ and 0 for even ‘numbers’. See examples on rows 11:14. We can exploit this pattern and use it in formulas that use Boolean TRUE/FALSE arguments. For example, Conditional Formatting.
3. We can use MOD with 1 as the divisor to return the decimal portion of a number. See row 15. This is handy for extracting the time from a date-time serial number.
4. If the divisor is 0, MOD will return a #DIV/0! error.

Download the workbook

Please Share

If you liked this please click the buttons below to share.