Rounding a number to the nearest integer or the nearest 0.5 or 5 or 10 multiple is a common task for many people. For example, if you’re a project manager involved in effort estimate, you can’t have 12.7 full-time resources working on a project. You need to round-up this number to 13 (the nearest integer). Similarly, if you’re buying stocks that are sold in a batch of 5, and you can afford a maximum of 123 shares only, you need to round it down to 120 (i.e., the lower multiple of 5). While you can do this manually for a couple of values, doing it for hundreds of such value could become tedious and highly prone to errors. There are a couple of great functions in Excel that allows you to quickly round to the nearest integer or the nearest 0.5 or 5 or 10 multiple. In this tutorial, I will show you how to use the MROUND, CEILING, and FLOOR functions to do this type of rounding in Excel.
Round to the Nearest Integer in Excel
Taking the example of project management, suppose you have a dataset as shown below where you want to quickly find out the number of resources needed for various projects that you’re managing.
Note that the ‘FTE Needed’ column has the values in decimals (calculated by dividing ‘Est Time’ with ‘Duration’). In this case, you may need to convert these values into the next integer. Here is the formula that will do this for you:
ROUNDUP formula takes two arguments – the number to be rounded and the number of decimals to round it to. Since in this case, we are looking for integers, we have used 0 as the second argument. In case you want to round to the lower integer, you can use ROUNDDOWN formula as shown below: =ROUNDDOWN(D2,0)
Round to the Nearest Multiple of 0.5 in Excel
Now suppose you have the same dataset as shown above, but now you can assign a 0.5 resource for a project. In such cases, you would want to round a number with a decimal part:
Less than 0.5 to 0.5 More than 0.5 to 1
The following formula can be used to do this:
CEILING.MATH function takes the number and rounds it up to the specified multiple to which you want to round up. In our example, since the significance value is 0.5, 6.71 becomes 7.0 and 7.29 becomes 7.5. Note that Excel also has a CEILING function which works the same way. It has been kept for backward compatibility purposes.
Round to the Nearest Multiple of 5 in Excel
To round to the nearest 5 in Excel, you can use the MROUND function. Suppose you have a dataset as shown below where you want to round the estimated number of hours to the nearest 5. This would mean that 161 should become 160 and 163 would become 165.
Below is the formula that will do this: MROUND function takes two arguments. The first argument is the number that you want to round, and the second argument is the multiple to which it should round. In this case, since we want to round to the nearest 5, I have used 5 as the second argument. Keep in mind that this doesn’t necessarily round to the higher or lower number. The result would depend on the value. In this example, if the decimal part in the value is less than 2.5, it becomes 0 and if it is more than or equal to 2.5, then it becomes 5.
Round Up to the Nearest Multiple of 5 in Excel
In the above example, MROUND function would round to the nearest 5 based on the value. This could either be a round up or a round down. But what if you want to only round up to the nearest 5. Then you can use the CEILING.MATH function. Here is the formula that will round up to the nearest 5.
Round Down to the Nearest Multiple of 5
To round down to the nearest 5, you can use the below FLOOR.MATH function:
Round to the Nearest Multiple of 10 in Excel
Taking the same example (dataset shown below), if you want to round off the number of hours to the nearest 10, you can use the MROUND function.
The following formula would round these numbers to the nearest 10: In this case, since we want to round to the nearest 10, I have used 10 as the second argument. Keep in mind that this doesn’t necessarily round to the higher or lower number. The result would depend on the value. In this example, if the decimal part in the value is less than 5, it becomes 0, and if it is more than or equal to 5, then it becomes 10. In case you only want to round up or round down to the nearest 10, use the CEILING.MATH or FLOOR.MATH functions. Here are the two formula that will round up to the nearest multiple of 10: Both these function would give the same result. Similarly, if you want to round down to the nearest multiple of 10, you can use the below formulas: In case you’re wondering what’s the difference between the MROUND and CEILING/FLOOR functions, here is a comparison of results. In case you think the results of MROUND and FLOOR function are same, look again (hint: Project D). Hope you find the methods shown in this tutorial useful. In case there are other ways to get the rounding done, do share with me in the comments section.
Convert Date to Text in Excel. How to Convert Formulas to Values in Excel. How to Calculate the Number of Days Between Two Dates in Excel. How to Stop Excel from Rounding Numbers How to Display Numbers as Fractions in Excel (Write Fractions in Excel)
