Join Dennis Taylor for an in-depth discussion in this video Finding the remainder with MOD and using MOD with conditional formatting, part of Excel 2010: Advanced Formulas and Functions.
- View Offline
If you have that extra moment and you're exploring some of the many functions in Excel--most of us don't have the time to do that-- but if you went to the Formulas tab and were looking at some of the math functions, I don't think you would be initially attracted to, or have any reason to go to, a function called MOD. If you see the description, it looks a little vague: "Returns the remainder after a number is divided by a divisor." And why would you ever want to use that? That was certainly my rationale for many years. Then I saw a situation where it was the perfect function.
Here's a situation here where in column A we've got the names of some items, and we've got so many of them left over. There's 162 of these. Maybe this company is consolidating or it's moving its distribution center. They have got 162 of these items. Now these items are all different, and they do have containers that we can put them into, but for this particular item we can only get 16 per container. If we start using the containers to put these items in here, how many are we going to have left over? In other words, if we divide this by 16, you know it's not going to come out even.
We are going to have two of them left over. And of course, we want to take care of all these at once. So what we are talking about here? The function called MOD. It calculates the remainder in a division. Here's the number we are looking at. Comma. Here's the divisor. It's not going to tell us how many boxes, how many containers we need; it's going to tell us how many are going to be left over: two. Of course, some of these you can do the math in your head; others you can't. So let's double-click. Copy this down the column. Here and there, of course, we have got a perfect fit. 168 divided by 24 is 7.
We don't care about that, but how many are left over? None. You see what's happening here. So it's an ideal use. It's dividing these and then calculating the remainder for us. How many items are we going to have left over? I guess we have got quite a few. One more and we could fill up a container. Now there's another application here too, and not so obvious. Sometimes you want to format a worksheet so that every other row is of a certain color possibly. Many of you know that perhaps the better way to do this would be to go to the Insert tab-- you can also get the here on a Home tab--and turn this into a table.
Now you may not want some of the side features, but we see what's happening here. And if you don't like those colors, well, go for it. You got 62 other choices out here. Have fun with that. Let me press Ctrl+Z to undo this. But sometimes you want control of it: Maybe you are going to be printing this. You might be using it for display. Maybe you want every fifth row to be a certain color, or every tenth row, or whatever. Let's select the entire worksheet and use conditional formatting in combination with the MOD function. This is on the Home tab. Conditional formatting is not build in to any of the standard capability here, but we do have the ability to create what's called a new rule.
In this particular dialog box here, choose Use a formula to determine which cells to format. Now in English, here's what we are about to say. If the row number of a cell is evenly divisible by 5--and of course that means row 5, row 10, whatever--then we want to make that row yellow, blue, or whatever we want equal. Now the entire worksheet is selected, but the active cell is in cell A1. So that's the cell we will use in the rule. Not an obvious thing, but once you use it, you will get comfortable with the idea. Equal. And we want to use the MOD function.
Then row, the row number of the cell A1 comma 5, meaning we want to take the row number of every single cell in this worksheet, one by one as it gets encountered, and essentially divide it by five. And when the remainder is 0-- in other words, that's when it's evenly divisible by 5--we don't put in the division symbol. When it's equal to zero, then it's evenly divisible by 5. Then we want to apply a format. Let's just say yellow. Click OK.
Again, the more you look at this, the more it makes sense. But if you only see it occasionally, you have to kind of think it out a little bit. Even though we're only using A1, we have selected the entire worksheet. Think of it as a surrogate or substitute for all the cells in the worksheet. Every time every single cell is evaluated, its row number is divided by 5 and if the remainder is 0, we are going to make that cell yellow. And there it is. Of course, if you want every fourth row or every third row, you just tweak that slightly and you use the number 3 or 4 instead of 5.
So you see a different use of the MOD function. So either in that example, or in the example we used here, sometimes you will encounter functions and have no idea where you might want to use them, and yet this makes perfect sense. If you are a mathematician, you might easily gravitated toward this little faster, but nevertheless it works just great.
- Referencing, copying, updating, and converting formulas
- Using the logical functions and creating compound logic tests
- Searching for and matching data based on specific criteria
- Reconfiguring cell data using text functions
- Calculating dates, times, and days of the week
- Analyzing mathematical and financial data
- Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
- Working with rounding functions
- Returning cell references