Do you want to round the working time to the nearest 15 minutes? I will show you how to do it.
Round time to nearest 15 minutes MROUND, FLOOR or CEILING functions
We can round our working time to the nearest multiple of 15 minutes. In most cases, when we round in Excel and reach the middle point, we start to round up. In the case of 15 minutes, the middle point is 7.5 minute. Now, let’s start the rounding. It’s a simple task. We can just use the MROUND function, write the number, which in our case will be the time, and then write the multiple as time, i.e. in double quotes. In this case, the first two digits correspond to hours, and the last two ones correspond to minutes. If we need seconds, we just write them further (Fig. 1)
=MROUND(A2,“00:15:00”)
Fig. 1 MROUND function
Now, let’s check our time. In the first three examples, we didn’t reach the middle point, so we go down. However, after we pass the middle point, which is shown as a threshold, we start rounding up. In the last but one example, we have exactly the same value, so we leave it just as it is. However, in the very last example, we didn’t reach the next middle point, so we still round down (Fig. 2)
Fig. 2 Rounding with the MROUND function
Sometimes, we always want to round down. In this case, we can use the FLOOR function. In the first argument, we have to write the time. In the second one, which is called significance, we write the same multiple as in the MROUND function, which is time in double quotes (Fig. 3)
=FLOOR(D2,“00:15”)
Fig. 3 FLOOR function
In this case, we always want to round down. It means that even when we go to halfway, we still go down. What’s more, even if we are very close to the next multiple, like in the last but one example, we still round down. Even, when we pass the multiple, we still round down, but this time to the next multiple, as in the last example (Fig. 4)
Fig. 4 Rounding with the FLOOR function
When we want to round up, we can use the CEILING function. We write the same arguments as in the previous functions. In this case, it’s enough if we pass the previous multiple by only one second, and the rounding will go up. Moreover, when we pass the next multiple by only a tiny bit, let’s say one hundredth of a second, we will go up to the next multiple (Fig. 5)
Fig. 5 Rounding with the CEILING function
Summing up, when we use the CEILING function, we always go up. When we use the FLOOR function, we always go down. And when we use the MROUND function, we go to the nearest multiple. The most important is the middle point. We can, of course, write any time we want in the second argument of those functions.
Do you want to know how to round numbers? Follow me.
Round to nearest multiple MROUND, FLOOR or CEILING functions
Let’s assume I get paid for correcting text and I don’t want to work with tiny amounts, like individual cents. What may help me is rounding the number of characters checked by me in a given text. When I round it, my income will be rounded as well. Let’s assume that I want to round to the nearest multiple of 200. In standard Excel rounding, when we go to the middle of the number, which in our case is 100, the rounding starts to go up. It means that when we use, e.g. the MROUND function to round the nearest multiple of 200, in the case of 2349 we go up because we passed the threshold of 2300. However, in the case of 2499, we go down. Why? Because we haven’t reached the middle point. But when we reach the middle point, which in this example is exactly 2500, we go up. In order to go up, we only have to reach the middle point, and when we don’t reach this point we go down. It means that it’s even for me and for my customer (Fig. 1)
Fig. 1 Threshold and rounding
Sometimes, there are situations that I always want to round down or always round up. Let’s assume that rounding down will be a little bit nicer to my customer, and rounding up will be nicer for me. How can we do it? In Excel, when we always want to round down, we can use the FLOOR function (Fig. 2)
=FLOOR(D2,200)
Fig. 2 FLOOR function
We have to remember that we are still using the rounding to the nearest multiple of 200. Our rounding goes down even if we passed the halfway. Even, when we are very near and almost reached the point of the multiple, and even if the difference is a very small fraction of an integer, like in 2599.999, we still go down. And when we reach the nearest multiple, which is 3000 in our case, it becomes our base and we will still go to this base until we reach the next base, which is the next multiple of 200 (Fig. 3)
Fig. 3 Rounding down
When we want to always go up, we can use the CEILING function, where we also give it a number and significance, i.e. a multiple, as it was with the FLOOR function (Fig. 4)
=CEILING(G2,200)
Fig. 4 CEILING function
When we pass our next multiple, we always go up. Even if the number passed the nearest multiple by only a small fraction, like 30000.0001, we still go up (Fig. 5)
Fig. 5 Rounding up
Of course, 200 is only an example of a multiple for those functions. We can write there 500, or use even decimal numbers, like 0.5 or 1.5. However, the example above used simple, hole numbers.
Today, we are going to talk about bank rounding, or half-way-even rounding.
Bankers Rounding (Half-Way-Even) to Dollars, Pennies and Hundreds
In a classic Excel rounding, if we have 5 as the most significant number for us, we always go up. It means that errors go up. To make the distribution of our roundings a bit more even, we sometimes need to go down. That’s why we have the bank rounding which goes around the nearest, even number. Let’s take the number of 7.50. Our whole number, which is 7, is an odd number. By rounding up, we go to the nearest, even number, which is 8. However, if our whole number is even, like in 8.50, we go down to 8. The distribution of the rounding values is more even, which means that an error is less significant. It’s important especially in banking, that’s why we call it bank rounding (Fig. 1)
Fig. 1 Rounding to the nearest, even number
How can we use it in Excel, when we know that the standard ROUND function will always go up if the most significant number is 5. The answer is that we have to create a different formula. The formula that works for me, where I haven’t seen any errors is a bit complicated, however, when we start working with this, it will be quite easy to understand. First, we have to check whether our numbers are half way or not. We can see that two numbers are half way. In the remaining ones, the situation is clear, i.e. we know that we want to go up or down (Fig. 2)
Fig. 2 Halfway numbers
If we want to check whether to go up or down, we can use the MOD function, where we should write 1 to check what the divider is, i.e. we want to check what is the rest after dividing it by 1 (Fig. 3)
=MOD(A2,1)
Fig. 3 MOD function
We can see the results (Fig. 4)
Fig. 4 MOD function results
If the number is halfway, we need to round it to the nearest whole number. It means that we have to check whether the result of the MOD function is equal to 0.5. If it is, we should use the MROUND function that returns the desired number rounded to the desired multiply of even numbers from our example. It means that we want to round 207.50 to the nearest multiply of even numbers, which is 2. In other cases, we just use the ROUND function to whole numbers. In our case, the number of digits is 0 (Fig. 5)
=IF(MOD(A2,1)=0.5,MROUND(A2,2),ROUND(A2,0))
Fig. 5 Proper formula
Looking at the results, we can see that when we have an odd, whole number and we are halfway, we go up to the nearest even number. If we have an even number and we are halfway, we go down to the nearest, even number. However, if we pass the halfway threshold, we start to go up (Fig. 6)
Fig. 6 Even and odd numbers in data
Now, let’s go to the rounding to 100. We copy our previous formula, which aimed at rounding to whole numbers. Now, we want to round to 100, which is 100 times more than in the previous example. It means that we have to multiply all numbers in our formula by 100 (Fig. 7)
=IF(MOD(E2,100)=50,MROUND(E2,200),ROUND(E2,-2))
Fig. 7 Multiplication by 100
And just like that we have proper results. We went halfway in two cases. Since we’re rounding to 100, we went up in the number 2350. In the second halfway number, which is 2450, we already had an even number, so we went down to the nearest even number (Fig. 8)
Fig. 8 Rounding to 100
If I change numbers in the last example and pass the halfway threshold, we can see that the rounding goes up (Fig. 9)
Fig. 9 Rounding goes up
If we want to use the bank rounding to decimal places, e.g. for pennies, it will be a bit more problematic. Here, we have to remember that we are checking the second digit to know whether it’s an even value. If the digit is odd, we round up, and if the number is even, we go down to the nearest even number. We have to remember that if we pass the halfway threshold, we start to go up (Fig. 10)
Fig. 10 Rounding with decimal places
If we want to work with this banker’s rounding rule with decimal places, we cannot use the MOD function because, when we work with e.g. pennies, the MOD function results will have some inaccuracies in digits that are far away, like on the fifteenth place after the dot, or so. In such a situation we won’t be able to check whether it is halfway, because the far places show that it’s not (Fig. 11)
Fig. 11 Inaccuracies in digits
When we work with whole numbers, we can use the MOD function because they are precise. A number in halfway will be returned by the MOD function as still a halfway number. In some other situations there can be some inaccuracies, but they don’t concern us, because they will work correctly. Here, we care only about halfway numbers, which stay halfway in the MOD function (Fig. 12)
Fig. 12 The MOD function with whole numbers
In the case of pennies, we have to use a different method. This method will show the number of digits we want to have. Let’s assume that we want to have five digits after dot. In such a case, we have to change our number to text by using the TEXT function, where we write the format number as 0.00000 (Fig. 13)
=TEXT(H2,“0.00000”)
Fig. 13 Changing numbers into text
Just like that we have numbers as text, but with five-digit precision. Now, let’s look for halfway numbers. We can see that halfway is 500 as the last three digits (Fig. 14)
Fig. 14 Numbers with five-digit precision
Now, we have to take out those three digits. Since they are located on the right side, we can use the RIGHT function, where we write 3 (Fig. 15)
=RIGHT(TEXT(H2,“0.00000”),3)
Fig. 15 The RIGHT function taking out the last five digits
And just like that we have only three, last digits. When we are in halfway, we will have exactly 500. Here, we also have to remember about different roundings. If the number before 500 is odd, we go up, but then it’s even, we go down (Fig. 16)
Fig. 16 Three, last digits
We have to check whether the number is equal to 500 using the IF function. We have remember that we are working with text, so we need to write 500 in double quote. Then, we can simply use the MROUND function, just like in previous examples, but this time our multiply isn’t 2, but 0.02. If we aren’t halfway, we can simply round our values using the ROUND function (Fig. 17)
We can see that the rounding went up when the significant number was odd, and the rounding went down when the significant number was even. When we passed the halfway threshold, we started rounding up. We have a formula that works with decimal places and the banker’s rounding, however, we have to remember about our assumptions. We set the precision to five digits, but only the last three of them are important for us. Based on the last three digits, the rounding uses the MROUND or the ROUND function (Fig. 18)
