The Hidden Power of the MOD Function in Excel | Alan Murray | Skillshare

The Hidden Power of the MOD Function in Excel

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
5 Lessons (17m)
    • 1. What is the MOD Function?

      1:53
    • 2. Example 1 - Extract the Time from a Date-Time Cell

      2:21
    • 3. Example 2 - Prevent the Entry of Odd Numbers in a Range

      2:44
    • 4. Example 3 - Calculate the Total Balls Bowled from Overs in Cricket

      3:47
    • 5. Example 4 - Sum Every Third Row in a List

      6:07

About This Class

503df43b

In this class, we look at some examples of why we appreciate the MOD function in Excel.

It is a function with a very specific purpose, and many hidden (and very useful) reasons for its use.

In the class, we explain the MOD function and what its purpose is. We then go on to explain 4 different examples of how it could be useful in Excel.

As usual, the files I use in my video demonstrations are available to download and follow along from the project area of the class.

Check out the hidden power of the MOD function, and if you need anything please get in touch.

See you inside the class.

Transcripts

1. What is the MOD Function?: Hello and welcome to this video tutorial from computer girl guard dot com. In this video, we're going to look at four examples of how to use the mod function of excel. Now, this is a clever little function that produces the remainder after a number is divided by another number, a divisor. So I'm going to begin with a quick at intro of what the mod function isn't had to use it. And then we're going to see those four interesting examples of what is capable of doing and how it may help you in your spreadsheets so quickly. Here we go Quick intro. Why? I have some numbers in column B. I have another number in See, which is the one I'm going to divide the of one by. So I produced Ahmad functioning D Free here is simply equals mod open bracket. He asked me for a number and then the divisor, So the number here is be free and a divisor. Here it's see free. And when I run this one, it produces the answer of zero because where number eight is divided by two, there are no remained er's that goes in beautifully. But if I copy that function down. I can see that the 2nd 1 has produced the remainder of one, and the last one produces a remainder or three when 15 is divided by six. So that's its basis. So his basic purpose in life, that's what it does. But our phone so many different reasons that you may see a benefit to that. And hopefully, by the end of this video, we're going to see some of those. 2. Example 1 - Extract the Time from a Date-Time Cell: So in the first example off the mod function, we're going to see how it can be used to extract the time from a sale. Will it contains the date and time. So I've got one of those in a too sick for July 2017 and quarter past 10. Now, maybe I want a column off just the times I don't need today involved. What amount function can help with this. As you may already know, the date and time and Excel is stored as a number she wants to look at so a two in a general format. That's what it looks like 4 to 92.4 to seven or eight. Now I'm just interested in the point for 4 to 708 park the time, the fractional parts. So it stands to reason known what the mod function could do. If I can get mode to divide that by one that will separate that fractional part the time and then if I make it look like the time then I have done my job, said Let's get that done much function in Selby to right now. The number will be to sell containing the date and time, and the divisor is just one type that in this time close bracket press enter that will produce an answer for me in here. It is quite ugly looking answer. But then, just with a little bit of format in, we can get it to just show the time part off the answer. And there we have it. So the initial answer produced the date as well as part of that. But we were just interested in the time parts. A little bit of formatting did that, but what function has extracted just that time? So it's different toe only doing the formatting part where we give the appearance off only the time all of Johnny's cut that number down and then formatted it for the appearance part . Senators. Example. Number one of What the Mod Function Condo's 3. Example 2 - Prevent the Entry of Odd Numbers in a Range: Okay, so here is example Number two, where we will see the motor function being used to prevent the entry off odd numbers in a range. Let's imagine that this is something that you need to do or much function can help because we know what more does it produce? A. Remained up when a number's divided by number. So it stands to reason that if we divide a number by two, if that goes equally, then it must be an even number for that happen. So if we can put that is a test in a data validation role, we can use that to prevent the entry of anything that doesn't meet that test. I e. Any odd numbers? We could flip that on its head and use it to prevent even as well. But it's examples about the old numbers. I'm going to highlight a range in this example just hard on a two to a 10 and I'll open up my data validation rose against choose a custom option for what to allow so that I can write in a formula, and this is going to be a mod function. Now let me write this in the cell. First of all, actually another copy and paste in there after so we can see what I'm writing now going to put the mod function in. I'm going to divide the first sell of that range A to black, the divisor of to close bracket. And I want to know if that is equal to zero. If it is, it called 20 that is an even number. And that is OK. They have to meet that condition and feels must be alone Number which is coming to here and take a copy off that formula. Let me hide up my range. A gain Data validation rules Retracing my steps here Heisted in and click. OK, let me get rid of that in there. So now in a two to a 10 range. If I type it four is quite happy with these entries of a type 12 it's OK by tight number seven. Now we have something that does not meet the data validation role. That is an odd number. It does not devise equally by it. Number two. So that is their second example of how the mod function could be off benefits. You 4. Example 3 - Calculate the Total Balls Bowled from Overs in Cricket: okay. Example. Number three now. And we're going to see how the mod function can help us to calculate the number off Bulls bold in cricket over its Now This is something that I have done in my sports league tables . Course so for anyone enrolled in that course will be familiar with this. Maybe, but I got a section on calculating cricket statistics and create data, uh, to help us kind of rank in a league table board in a kind of cricket tournament and stuff. I wanted to bring that here because the marked function was involved in quite a similar way , really, to how we used in the time in example, Number one. So for those who are not so familiar with cricket in column B, this is held. A number of overs is normally shown which a number of balls bowled. We savings at 6.2 overs, 9.4 overs. Eso There are six bulls to an over, and then the 0.4 because a 9.4 it's just four more bowls. It was not a former boss is not a complete over. So we're going to do here is we're going to use the mod function to extract that fractional part again. The point to 0.0.3 point four. I'm gonna put that in addition to the integer part. So we're going to use a function called int here in addition to mode. So a bit of a double bubble here. We've got two functions that we're gonna loan. Fantastic. Let's get this done. So see, free equals int envelope in brackets. I'm going to use the int function on cell. Be free to extract just the inter portion, which would be free, is the six. And I'm going to multiply that by six to find out how many bulls in Toto were bold. 26 overs, six balls and over six times six. That is 36 balls. I'm then going to plus on that here is the multi function It comes mod function off be free comma one divided by one. Just like the extracting time example that will bring out the fractional part off that cell lows bracket. I've got to multiply that by 10 because in a case off, for example, 6.2 overs, that is point to. But that is two balls. So when I run the motor function that would extract the answer as no point to which on its own is not very helpful. That's not really any better than what it was originally, so they're gonna multiply it by 10. Just a shift. That, too, along the decimal places. So it turns were no point toe to to at the two on top of the 36 38 balls were bowled. Copy it down, and now I have a formula that can calculate the total number of balls bowled from the way that cricket stats normally represent number off overs. And once again, example Number three there had a mod function was able to come in and help us achieve that came with a friend, this time going by the name off inch to give a That task is dumb. 5. Example 4 - Sum Every Third Row in a List: Okay, So in the last lesson off mod function examples, we're going to see how it can be used to some every n throw and in this example, onto some every third road every third sell off my data set of my range of cells. Now, we need the mod function, obviously right now, but I'm also gonna use this some product function. Now, this is a function that has its own section of this course as a feel free to go and check out that section. If you haven't, I'm already. So you get to grips with how that works that maybe before this example. But yeah, you may not need it. Don't worry too much because, um, additional learning there and also outside of this course. So in Selby One, that's why I want a total from Huntingdon knowledge and read in. I was known a sales B five, b eight and b 11 but I'm not interested in the stuff between them. So in Selby one, let's start up my some product function on a maze in function, this one that's going to prompt me for summarize. Now I need to have my conditional element of this formula to calculate that whether that is a number that should be summed, is it in the first cell? And then I need to actually provide the numbers to some which will be column B essentially . So the first thing I'm going to do is open a bracket at four on a ray here on going to put in my mod function. This mud function is going to help me with the conditional work. Now it's going to prompt me for a number, and then what? To divide it. By now, we've got another function to help out here, and that is road a function. It will return the road number of a reference so quite simple, really up on opening bracket After that function, I'm not going to provide it with my range off numbers and again to fix the up and close the royal function. Sweat the moment all this road functions going to do, this is going to look at a Ronan mango your row free. You're right for your row five. Nothing particularly amazing on its own, but that's going to be very helpful in this formula. After that row function, I want to subtract two now. This is because I've got these two kind of redundant rose at the top. It all starts happening from road free, Really? So when this formula looks at that first cell and road function says, Oh, you are row free under subtract and go noses. Row one. That's the first row off my data. Maybe the third row with sheet, but it's the first round of my data on I'm interested in every third row, and the third row of data is actually in Rhode. Five. So the road functional come up go your road. Five ago. Note. Tech to off That's deferred wrote. That's the one you want to add. And someone so forth. Theeighties Row six and the Elevens wrote nine. They're all motor pool's off free, So after subtracting two, I can put my common type free as the divisor. So once you have done that calculation, like the Curro number, take away too divided by free. So if you do that in the answer comes back zero, then it must be a multiple free. It's in one off the third rose. If it doesn't, it's no, ignore it. So after my free, I'll put the closing bracket for that mod function and I'll ask you is equal to zero and close off that array. So that's the condition. Is the row number off my data set a multiple free, and we know that if it comes about 30 after doing that, Ma jealous, it will be a multiple free. So once you've got that information, I'm gonna multiply that by the range of numbers. Now this some function and close off my two brackets. This some function once it's done that conditional work. If it is a multiple free, if it does come back with zero, it will return the result off. One. That's what it does. If it doesn't return is up. Zero it does. It returns. It is one that is in multiplied by the number. So get zero multiple by 500 euros. Zero model of 701 multiplied by 1200 and then once it's performed, or that it will perform this summer in the end and add them all up and we have Iron's. If I press into on here, the answer is 2850 Eso yes, 1000 perfect 1200 2200 plastics 52 85 It is only adding up every third cell of their. As you can imagine, this function can be altered quite easily. Just change that little divisor there. So if you need to add up every other row or every fifth row, every 10 throw that can be worked in the modern function is the major player here. It's one of the major place. There's no give you all the credit. Some product for important, too. But the module is's, you know, during the hard work of making sure you are one of the third ropes and in some products great for that intense, conditional work.