There are several different functions in Excel focused on rounding different numbers. So I'm going to talk about rounding functions and also remainders in this screencast. The rounding functions that are found in Excel: INT, that's the integer function, TRUNC, TRUNCATE, ROUND, ROUNDUP, and ROUNDDOWN, and then we have something known as MROUND, and we have CEILING and FLOOR. My goal is by the end of this screencast is not to have you guys all going crazy. There are very small differences between some of these functions. If you're struggling with understanding all the little details then I wouldn't worry too much, you can always refer back to this screencast or other reference materials. So let's take a look first at the INT function for integer. The INT function rounds down to the nearest integer. So always down regardless of whether you're talking about negative numbers or positive numbers. An integer is just a counting number. So if we took 3.2 and we took the integer of 3.2, we always go down, so that'll be three. Similarly, 1.9 we would go down to one. Negative 3.2, again, we go down, so that's going to go to negative four, and then negative 1.9, we go down and the result of that is negative two. The important thing here is that the arrows always face down. If you're looking at it in a diagram like this. The TRUNC function, for truncate, just removes anything to the right of the decimal point. Is basically scissors. If we have 3.2 and the trunk of 3.2, we just keep the three. So everything left, we keep, everything right, we get rid of. If we have 1.9 TRUNC of 1.9 is one. TRUNC of negative 3.2 is negative three, negative 1.9, that's negative one. So the INT and TRUNC functions are quite different, even though they both result in integers. Let me just show you how we can do this in Excel. I can type in, I'm going to do the four numbers over here. Integer of 3.2, we can copy that down. We can also put in the TRUNC function of those numbers. I can copy that formula down, and you notice that there are a couple of differences especially when you get to the negative numbers. Let's talk about rounding functions. All three of these have the same arguments. We have a number and we have something known as the number of digits. So let's consider in this number, 123.456. Let's first talk about the number of digits here. The ones place is reserved for zero as the number of digits, and then going rightwise we have the tenths place, we would refer to a one. The hundreds and thousands would be two and three. We can also go left from zero. So the tens place is negative one for the number of digits, in the hundreds place is negative two. So let me show you how we can use the ROUND function on this number here. By the way, this is very, very important. If you click in a cell like this and you go up here to the number tab and we want to decrease the decimal. So I have decreased that to 123. That is not in any way the same thing as rounding. Because if you go up here to the Formula bar, Excel is still storing that number, 123.456. We haven't truncated the 0.456, and that's what rounding does. Let me just increase decimal there. I'm going to go ahead and put in the ROUND function of our number up here. Press F4 to make it an absolute reference, and then I'm going to do to the number of digits here on the left and so three corresponds to the thousandths place. When you do that and you see that we haven't truncated that at all because that number is already truncated or rounded to the thousandths place. I can drag that down and now I'm using two as the number of digits. That second argument, we're rounding that to the hundredths place. We can round to the tenths place, to the ones place, and then we can round to the tens place, and finally the hundreds place. The ROUNDUP and ROUNDDOWN functions are very similar to round down in that they have the two arguments, the number and the number of digits. ROUNDUP always rounds away from zero. So if we had the number 3.2 and we perform the ROUNDUP function on it, we always go away from zero, so that would be a four if we use zero as the number of digits. Similarly, if we have 1.9, it's going to round it up to two. Let's take a look at negative numbers. Negative 3.2, again, we're always going away from zero, so that would be negative four. Negative 1.9, we're going away from zero, so that's negative two. The important thing with the ROUNDUP function, these arrows on this diagram always face away from zero. The ROUNDDOWN in contrast always rounds towards zero. So ROUNDDOWN of 3.2, 0 is going to be three, 1.9 would be one. So we're rounding down to the nearest ones place because I have a zero as that second argument. ROUNDDOWN of negative numbers, negative 3.2, we always go towards zero, so that's negative three. Negative 1.9 is going to go to negative one, and that's how the ROUNDDOWN function works. The important thing here is that the arrows always face towards zero. Let me show you how we can perform the ROUND, and ROUNDUP, and ROUNDDOWN functions. So we're going to put our number in there and then the number of digits. I'm just going to use zero for all of these examples, but we can certainly use different numbers in there. So this is rounding to the nearest ones place. You see that we always round to the nearest integer. If we now punch in the ROUNDUP function, then we're always going to round up. So that means away from zero. The ROUNDDOWN function, we're rounding to the significance of zero, which is the ones place and this always rounds towards zero. The MROUND function is similar to the ROUND function, but we can round to the nearest multiple. The multiple in this example is going to be 0.25 and if we visualize this on a number line between four and five. If we have the number 4.8 and we do MROUND of 4.8 with that second argument 0.25, that's our multiple, it's going to round to the closest 0.25 and it's going to go to 4.75. MROUND of 4.4 is going to go up, in this case, rounding to the nearest 0.25 and that's going to output 4.5. There's two more functions. CEILING and FLOOR. CEILING is very similar to MROUND, but it's going to round up to the nearest multiple. In this case, in the CEILING function is technically referred to as significance although that's basically the same as multiple. So if I put in CEILING of 4.8, 0.25 is going to round up to the nearest quarter. If I look at 4.4 and I do CEILING of 4.4, then that goes up to 4.5. So we're always going up to the nearest multiple. FLOOR is essentially the same, but we're going down. So a floor of 4.8, 0.25 is 4.75. FLOOR of 4.4 is 4.25. So we're always going down. We can also take a look at negative numbers. CEILING always goes up even if your negative, and if we did FLOOR of negative 4.8, that's going to go down to negative five. So let's go ahead and put these into Excel. If I have my number here in my significance up here, let's make that F4, so we have an absolute reference. It's going to round, MROUND rounds to the nearest quarter. The CEILING function of our number over here with our significance level up here, it's always going to go up. So we're going to go up to 5, 4.4 is going to go up to 4.5. The FLOOR function goes down to the nearest significance level, 0.25 and I can drag that down. If I change my significance level maybe to 0.2, you see that those formulas will adjust. I wouldn't be discouraged by all the little nuances and minor differences between some of these rounding functions. But you can always look back at this screencast or other tools on the Internet, for example. The last thing I'm going to talk about in this screencast is the MOD function. It's also known as the REMAINDER. I think a lot of you are familiar with that term remainder. So let's take a look at the modulus of 7, 3. That's essentially the remainder when 7 is divided by 3. I think most of you know that if you take 7 divided by 3, that's going to equal 2 plus one-third. This down here is known as the divisor. We ignore this term and whatever is left on top of the divisor is known as the remainder or the modulus. A second example, when we take 100 and divide by 7. So the modulus of 100, 7. 100 divided by 7 is equal to 14 plus two-sevenths, and so this is the remainder. Let me show you how to do this in Excel. I'm going to go ahead and put the remainder also known as the modulus. So if I use the MOD function of our number in our divisor, we get one. So that's the remainder and I can drag this down. By the way, if you wanted to keep the number, so this is actually 7 divided by 3 is equal to 2, you can always use the integer function of the number divided by the divisor. So 7 divided by 3 is 2.333 and this just going to keep the integer part because the integer function always rounds down, and then we can drag that down. Maybe we want to rearrange this a little bit. I hope you enjoyed this screencast on rounding and remainders, and I hope you don't go crazy from trying to remember all the slight differences between all of these different rounding functions.
