Excel MOD Function - Easy Explanation & Practical Examples of MOD for Work

in this lecture I'd like to show you practical uses of the mod function one practical use is to control the data labels in charts another use is to report quarterly data now we're also going to take a look at how we can use mod to decide how we can group people or products together so what is the mode function - by definition it returns the remainder after one number is divided by another number now for me personally this definition didn't click immediately but once it did everything made sense so I'm going to explain it to you in a simple way let's take a look at whole numbers we can classify them into specific groups right so we can say we have odd and even numbers there are also numbers that are divisible by two or numbers that are divisible by three by four five and so on so that's what we can use the mod function for to check if a number is divisible by another number and if it's not how far away is it from being divisible by that number so basically mod answers the question how far am i and that answer is a whole number so let's do a simple example in Excel you write the mod function as follows it's too divisible by two yes there is no remainder so the answer to our mod function is zero what about mod three - well now it's not divisible so the question we need to answer is how far away is three from being divisible by two now do we look to the future or do we look to the past and the key here is that we look backwards to was fully divisible right and how far away is three from two one away so that's the answer and once we get to four so mod four to the answer is zero again so now what about mod one three how far is one from being divisible by three well zero could be divided by three right so it's one away and mod to three looks back at zero again and now it's two away and once it gets to three then it's divisible so the answer is zero so hopefully that's clear now how about negative numbers well they behave the same way in the sense that they always look backwards and backwards means they look to the smaller number so mod 1 3 is 1 but mod -1 3 is 2 we look to the next smallest number that was divisible and then count the distance in the first case it's 0 so it's 1 away from being divisible by 3 and what about minus 1 well 0 is bigger than minus 1 so we can't look there what number do we need to look to it's minus 3 and minus 1 is two away from minus 3 so that's the result of our mod function so that's the simple way to remember how the mod function works now I'm going to show you some real business cases where you can put the mod formula to good use in the first example we have a list of participants that we one is little groups so these are the number of participants that are going to attend our workshops let's say throughout the year and we want to create some exercise activities and we're not sure if we should split them into groups of six groups of three or groups of four we can use the mod function to let us know which one is a better fit because with mod we can see how many people are left over all we have to do is use mod first comes our number so that's the number of participants and then our divisor in this case it's six here is three and here is four but because I've written it as text I can use the write function to strip out that number I want the right of this and it just want the last number so that's one zero here means that when I have 54 participants I have no one left over I can form groups of six if I'm planning to pull this down and across I need to think about my fixing so with here I have to fix the column so I'm just going to press f4 three times that fixes a here I need to fix my row I need to press it two times I can get the total amount of people that are left over by just summing these up we can see that groups of three is actually the best match so you can use this not just for participants but for products as well if you have product cases that need to be shipped in let's say packs of six you can use the mod function to see if you have anything left over now in the next example we want to report quarterly headcount information we have raw data that's here we have our date our revenue and headcount and let's say this is a report that we need to complete and send to someone revenue is something we want on a monthly basis but headcount is something that we just want to show on a quarterly basis the first question our first challenge is how do I know if I've reached a quarter or not so what is the indicator for the quarter well here I have dates right and they are formatted as dates if I use the month function I can strip out the month from there this gives me the information on if I have reached a quarter or not and now I'm going to wrap this up in the mod function I'm looking for mod my number is the month and my divisor in this case because I only want to report quarterly data is 3 now I will know if I have reached 1/4 because it will be zero there will be no remainder so basically if I check that the answer to my mod function is 0 then I get the head count from here I need an if check is that this equals zero and then if it does what should I do get my head count since I have the data in a horizontal format I can do an H look up the horizontal look up my lookup value here you can see I have dates the date is my look up my table array is this I'm going to just fix this right now dicks number what do I want returned I want the headcount and that's number three and I need a false for an exact match now I move on to what should it do if the value is false and nothing just put double quotation marks that's that let me just make sure that I don't need to do any additional fixing some month hey that's fine and that's the one check so that was the only thing to fix whenever I reach a quarter I have the headcount data here in the next example we're going to use the mod function to show the data labels for every nth occurrence and our chart this is a sales development chart that shows our sales by period assume that this data is just coming out of your system and you want to design a chart where you control the data labels so notice here it's every 4th occurrence I can change it to 3 I will show the data labels for every third occurrence if I change it to 2 it's every second occurs except in the first instance so for the first point on the data series I'm always showing the data labels how can we go about designing this the first thing is that when you have this data and you just create a standard chart out of excel standard line chart you get this if you put a little bit of effort into it to organize your data and your criteria better you get this notice the difference between these two charts one is the axis it's much easier to read because we've split the year and the months we've also made it look less cluttered by just using the first letter of the month we've also added the data labels directly to our line chart so that it's much easier for the reader to remember these numbers and to remember the trends it's much easier to read this than to read this what I've also done is remove the y-axis because we don't need that once we add the labels here so what techniques do we need to get to this stage well we need a data preparation table so we have to take this data and change them in a dynamic way for our chart so we're going to prepare them for the chart because I'm splitting year and month separate I need a separate column for year and month like you see here notice that for year I'm only putting the year for January otherwise I have nothing I have empty cells because notice what happens if I input the year here see you get that tick mark and once you remove it what Excel does is it centers that across which really improves the readability of this chart in order for me to show the data labels in this way what I need to do is to add a separate series to the chart because remember whenever you want to conditionally format anything in your chart you need to have a separate series for it and since I have a separate series for it I can also format this series different to my original series so you can see that for the data label series I'm using these markers whereas for my original series here I'm not using any markers it's just a standard line series so let's just do this together I'm going to move this out of the way I'm going to delete all this data in fact I'm going to use clear all because I have some formatting in there that I want removed let's start with year I'm only showing the year if I reach January so basically I can use an if function together with the month function to see if every January or not if month of this equals one then show the year otherwise show nothing that looks good for month I only want to show the first letter of the month so instead of using any functions what I'm going to do is use formatting I'll just say it equals this period I'm going to copy this down and now I'm going to press ctrl one to go to custom formatting and I'm going to change the formatting of this to show me the first letter of the month if I put in m1s I get the number then I get 0 1 here I get the text I get the full text and now if I put one more M I get the first letter that's what I'm going to use here now before we move to data labels let's just take this original series so that was just a standard line chart that was inserted I'm just going to copy this and we're going to start by just changing this to our year and month now right mouse click select data we're going to edit the axis part and we're going to select these that's what we have we can even make it bigger if we want now let's move on to our two labels here is where the mod function can be really useful we want to show it for every 4th occurrence now it doesn't matter what the month is is every 4th occurs every 4th line one formula that can give us the occurrences the line number is the rule formula if I just write row I put in a 1 okay so basically it's this row a 1 [Music] I get a one when I pull this down this becomes Rho a 2 I get a 2 this is something I can use for the occurrence I can put this inside my mod function right I can say mod and my divisor is basically this number get that's something I need to fix let's see what we get okay every fourth occurrence I get a zero next step is to wrap this up inside an if function if the result of my mod is zero then show me the sales number otherwise and say show me nothing because that's the more logical first step approach okay so now I get the number and every fourth occurrence so let's just pull this down all the way let's add this series to our charts before we make any other modifications to add this I'm going to right mouse click select data add my series name is data labels always give your series a label so that you know what it is when you want to modify it later on and say okay what happens here that my line keeps crashing to zero because I said give me nothing and nothing in a formula the results that's basically return to Excel is zero so that's why it does the crash and if you want to avoid that for your line series instead of nothing use the n/a function so an a basically means write an error a specific error to the cell see what happens it's not crashing to zero as a next step what I'm going to do is modify the series by just showing the markers and not the line itself so we just have to double-click on the series now you see you have to be careful with your double clicking it's now click on a data point so to make sure that you have selected your entire series it's better you select it from here and that's why you need to give them all those names proper names that you remember so you know what to select we're going to select the full data label series in the options we're going to say we don't want a line but we want a marker well let's use this one in this case that looks nice as well for border no line and we can control the fill of the marker let's choose a dark color that looks good now for this new series the data label series we are going to add the data labels and we're going to put them on top so let's go to more data label options we're going to go to above that should be dynamic that if I change this to 5 this switch if I change it to 6 this switch I can remove this just press Delete and in the original version that I had I also had the first data label present that's up to you how you want to handle this you can change this formula and fix it that it always equals this for the first instance but sometimes I avoid doing this because I'm afraid that someone is just going to pull this down because they think they have the same formula so we might be better off if we just modify the entire formula by adding another if function here I could use the count a function just say count this to itself just fix the first part with f4 if the results of my account equals one then give me this number otherwise do the mod thing and then close brackets this one will only equal one here because when I stretch this down it's going to be two and then three and four and so on but this way at least I have one formula that I can apply to everything so these were three practical uses of the mod function that you can use to solve specific problems I hope you enjoyed the video and don't forget to subscribe

Channel: Leila Gharani

Views: 100,100

Rating: 4.9333334 out of 5

Keywords: Excel MOD function, MOD formula, Excel MOD, MOD for Occurence, practical example mod function, MOD for Reporting, excel remainder formula, MOD Quarterly, XelplusVis, Advanced Excel, Master Excel, Learn Excel Formulas, Excel formulas, Excel Tips, Excel Tips and Tricks, Best Excel Online Course, Excel Advanced formulas, Improve Excel skills, Excel for analysts, Microsoft Excel expert, Leila Gharani, Excel 365, Excel 2016, Microsoft Excel

Id: tLufL2M1TEs

Length: 19min 30sec (1170 seconds)

Published: Mon Jan 02 2017