I'm going to show you a couple of exciting examples related to rounding and remainder functions in Excel. First, I've got a list of times here. I want to show you how we can use the MROUND function to round these times. So the MROUND function can also be used for things like time to the nearest 15 minutes. So I've got those times, MROUND of the time. I can put 15 minutes in quotations, and press Enter. I can drag this down, and you'll notice that these are decimals. In order to get this to times, I have to go up here to the number tab, and I have to convert this to time, and you see that we've converted those to the nearest 15 minutes. So that's using the MROUND function. Maybe you want to round all of these up to the nearest 15 minutes. So we can use the CEILING function of our time. I can do the same thing with 0:15. We're going to round up to the nearest 15 minutes. Again, I can pull that down, but we need to convert that to time. Because remember that times are recorded in Excel as serial numbers. So that rounds up all our times to the nearest 15 minutes. A lot of times when you go shopping, you'll see that pretty much everything is rounded to the nearest $0.99. So 299, 399, and so on. So maybe you have some calculation in spreadsheet, and you're getting these values, but you want to convert to something that ends with a 99, we can use the MROUND function. So I can use MROUND of that dollar amount. If we round to the nearest dollar, that's going to round it to the nearest dollar. We could also just use the ROUND function here. But then what I'm going to do is I'm going to subtract one penny. Now, we've converted that 821 to the nearest 99 cent increment, and I can pull that down. So that's how you might convert some calculated prices to ending in 0.99 cents. If you wanted to change the significance, instead of to the nearest $1.99 cents, the second argument of our MROUND function, I'm going to change to that significance. I'm going to put in absolute reference there, and then I can pull this down. You see that if we're close to 0.49, so maybe we wanted to round to the either 49 or 99, you can also change this to even 0.1, and so you're just ending in a nine. A lot of times you'll see gas prices that they always end in nine, so nine-tenths of a penny. So maybe you have some calculation going on on a spreadsheet, and you need to convert this to have to end in a nine, so I can use the MROUND function. We're going to convert this to the nearest cent, and then I'm going to subtract 0.1 of a cent. What this does, if I bring this up, increase the decimal, it's going to make sure that all of these end in a nine. So it's rounding to the nearest penny, and then subtracting 0.1 of a cent. Let's say you need 76 eggs. You're doing a lot of cooking or baking, and we need to know how many dozens of eggs should we get. The MROUND function is not going to quite do it. Because what the MROUND function does, if I round to the nearest 12, which is a dozen, that's just going to actually round down in this case. That'll give us eggs. If I wanted to do dozen, I could divide by 12. So that's the closest dozen, but I want to round up. So if we wanted to round up, we could use CEILING of the number of eggs, 12. So that's going to round up to the nearest increment of 12, and then we're going to divide by 12 to get the number of dozen. So we're going to need seven dozen eggs, and then this will automatically update. If we have 113, we're going to need 10 dozen eggs, and so on. We can use the modulus function to determine if a number is even or odd. So I could just put in a couple of these. Let's go up to 10. There's a IF function. You'll learn more about this in a subsequent screencast coming up soon. IF the modulus of the number divided by 2. So when we divide that number by two, if that's equal to zero, then I'll put in even. Otherwise it's odd. So one is odd. When we take 2 divided by 2, that's equal to 1 with a remainder of zero. So that's going to be even. Three divided by 2 has a remainder of one, which is not zero, and again we're checking if it's zero, then we put in even, so that the second argument is the value of that condition is true. The third argument is if that first condition is false, and then I can drop that down, and we can determine if a number is even or odd. Similarly, I can determine if a number is divisible by three. So if the modulus of a number when divided by 3 is equal to 0. If that is true, I'll put in yes. Otherwise I'm going to put in no. Again, you'll learn more about the IF function real soon. I can press Enter, and then I can drag that down, and this'll tell us if a number is divisible by three. Finally, maybe you have a bunch of numbers in ounces and you want to convert two pounds and ounces. You probably all know that there are 16 ounces in a pound. So to determine the number of pounds in my ounces, I can use the integer function. If I divide my answers by 16, then that'll determine how many whole number pounds there are in that number of ounces. There'll be two pounds, and then we're going to have eight ounces leftover. So I can drag that down. To determine the ounces, I can use the remainder, the modulus function. So when we take 40, and we divide by 16, that's the modulus. Whatever remains is going to be the remaining ounces. So what this means is that 40 ounces is equivalent to two pounds, eight ounces, and so on. By the way, if you wanted to concatenate these both into a single cell, we can do something like this, and you'll learn more about this in subsequent screencasts. So I can use the ampersand to concatenate or join numbers and text. So I can do the pounds, and then I'm going to concatenate that with a space, pound, another space. I'm going to use the ampersand to concatenate that with the ounces. Another ampersand sign, a space, and oz. So what that does, it just concatenates or joins those numbers with the texts that I put in, pound and ounces, and I can drag that down. So that's a nice way just to take your ounces, something that's in ounces and convert it to pounds and ounces. So hopefully, these examples gave you a better idea of how we can use these rounding and remainder functions in Excel. Thanks for watching.
