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
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:
- The result of MOD will always match sign of the divisor. Compare examples on rows 7:10.
- 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.
- 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.
- If the divisor is 0, MOD will return a #DIV/0! error.
Download the workbook
Enter your email address below to download the sample workbook.
If you liked this please click the buttons below to share.
Leave a Reply