Here are a couple useful ways to apply the MOD function in Excel:
(Note: Video version above, text version below)
The MOD function in Excel is defined as “Returns the remainder after a number is divided by a divisor”. The syntax is =MOD(number,divisor). So basically, you enter a number and divisor and the MOD function will return whatever the remainder is. For example, =MOD(100,3) returns the number 1, since 3 goes into 100 33 times with 1 left over.
So what are a couple of practical uses of this function? One that we reviewed in the past is to conditionally format a data range so that alternate rows are a different color. To do that, highlight the data range, click on Conditional Formatting, select New Rule and then select “Use a formula to determine which cells to format”:
Enter this formula in the formula box:
Then choose your format style, click OK and OK again, and your data range is now formatted:
What is this formula doing? The “number” in the MOD function is the row number of the data range. The divisor is 2. For each row, when it divides the row number by 2, if it’s an even row there is no remainder, but in an odd row, the remainder is 1, which makes the MOD formula TRUE, and it applies the formatting defined.
Another application for the MOD function is packaging inventory. In our data range we have part numbers, our inventory and the quantity of each item that our packaging will hold. By using the MOD function, we can determine if we have any extra left over after we’ve done our packaging, or if we just need a couple more to fill out the boxes.
Here is the formula we will enter:
Now by copying that formula to the balance of the data range, we can see the extra items that we will have after our boxing is done:
And there you go! A couple good uses of the MOD function. Can you think of any applications in your work or at home? Feel free to leave a comment with how you might use the MOD function.