Round to N Digits or Multiples

In this tutorial, we will explore different methods of rounding values in Microsoft Excel. We will learn how to use various rounding functions and understand the difference between rounding through formulas and changing the displayed value through formatting. We will round by the number of decimals or whole numbers as well as to the nearest multiple. Let’s get started.

Video

Walkthrough

Let’s walk through the functions one exercise at a time.

Exercise 1: Using the ROUND Function

Let’s say you have a value in a cell like this:

If you only need to change the “displayed” value, you can simply apply cell formatting. This will change the value displayed in the cell, but not the underlying cell value.

For example, we can simply use the Home > Decrease Decimal to change the displayed value to show the following number (even though the underlying cell value is still unchanged):

In some cases, this is all you need.

But, depending on what you are working on, you may need to round the cell value because it is used in subsequent formulas or references. In this case, you may want to consider the ROUND function.

You pass it a number or cell reference, and then the number of digits to round (where 0 is whole numbers, positive numbers go to the right of the decimal, and negative numbers go to the left of the decimal), and it will round to the desired digit, independent of the cell formatting.

For example, we can use the following formula in B10:

=ROUND(B8,0) 

This will round to the nearest whole number:

Or, we could use the following formula:

=ROUND(B8,-3)

Which rounds to the nearest thousand:

If instead of rounding up or down as needed, we wanted Excel to always round up or always round down, we can use the functions discussed in the next exercise.

Exercise 2: Using the ROUNDUP and ROUNDDOWN Functions

If we always want Excel to round down to the provided number of digits, we could use the following:

=ROUNDDOWN(B8,0)

And, as you may have guessed, to force Excel to always round up, we could use:

=ROUNDUP(B8,0)

So, the ROUND, ROUNDUP, and ROUNDDOWN functions round based on the number of digits. But, what if instead you wanted to round to the nearest multiple, such as to the nearest 5, 10, or 25? Well, as you may have guessed, that leads us to the next exercise.

Exercise 3: Using the MROUND Function

The MROUND function rounds to the nearest multiple, such as to the nearest 5, 0.5, 10, 0.25, and so on.

For example, we could use the following formula to round the value in B7 to the nearest multiple of 5:

=MROUND(B7,5)

We hit enter and bam:

This works for whole numbers as well as decimals. For example, the following formula would round the value in B7 to the nearest quarter:

=MROUND(B7,.25)

We hit enter and bam:

And that is how to use the MROUND function to round to the nearest multiple.

Conclusion

Rounding in Excel is a useful capability when dealing with numerical values. By understanding the different rounding functions, such as ROUND, ROUNDUP, ROUNDDOWN, and MROUND, you can ensure accurate calculations downstream. If you have any suggestions, improvements, or questions, please post a comment!

Sample file:

FAQs

Q: What is the difference between rounding through formulas and changing the displayed value through formatting?

A: Rounding through formulas affects the actual values and how they interact with subsequent formulas, while changing the displayed value through formatting only affects how the value is shown.

Q: When should I use the ROUND function?

A: The ROUND function is useful when you need to round numerical values to a specific number of decimal places or to the nearest whole number.

Q: Can I always round up or down using Excel functions?

A: Yes, Excel provides the ROUNDUP function to always round up and the ROUNDDOWN function to always round down.

Q: What is the purpose of the MROUND function?

A: The MROUND function allows you to round values to the nearest specified multiple, which can be useful in certain situations, such as rounding to the nearest 0.5 or 100.