How to Round to the Nearest Integer or Multiple of 0.5 / 5 / 10 in Excel

Watch Video – Round to the Nearest Integer or the Multiple of 0.5 / 5 / 10 in Excel

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.

Round to the nearest integer - Dataset

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(D2,0)

Round to the nearest integer - ROUNDUP formula

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(D2,0.5)

Round a number to 0.5 - CEILING function

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.

CEILING function in Excel for backward compatibility

Also read: Convert Mm To Inches In Excel

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.

Round to the nearest 5 - example dataset

Below is the formula that will do this:

=MROUND(B2,5)

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.

=CEILING.MATH(B2,5)

Round up to the nearest 5 - formula

Round Down to the Nearest Multiple of 5

To round down to the nearest 5, you can use the below FLOOR.MATH function:

=FLOOR.MATH(B2,5)

Round down to the nearest 5 - formula

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.

Round to the nearest 10 - example dataset

The following formula would round these numbers to the nearest 10:

=MROUND(B2,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:

=CEILING.MATH(B2,10)
=ROUNDUP(B2,-1)

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:

=FLOOR.MATH(B2,10)
=ROUNDDOWN(B2,-1)

In case you’re wondering what’s the difference between the MROUND and CEILING/FLOOR functions, here is a comparison of results.Round to the nearest 10 - MROUND CEILING AND FLOOR

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.

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

2 thoughts on “How to Round to the Nearest Integer or Multiple of 0.5 / 5 / 10 in Excel”

  1. In using the Google-Type Suggestion search box with vba-codes … I used that feature and it is great! Now, I need vba-codes to clear that dropdown selection field-item selected, so other selection may be made, quicker (dynamically). Can you help me with vba-codes that I may use as a button to clear that field?

    Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster