Round to Nearest Value in Excel

Sometimes, displaying a very precise number is not useful and you may want to round the value. But there may be cases when you want to round the number to the specified nearest value and not to the nearest integer.

Example

The rounding function works like this.

It rounds the value to the nearest float value with a precision of 1.

This number 12.3456 will be displayed as 12.3.

Rounding the number to the nearest 10, 100, 1000

The simplest way to round numbers to the nearest 10, 100, or 1000, is to add a negative number to the second argument of the ROUND function.

Nearest 10=ROUND(A2,-1)
Nearest 100=ROUND(A2,-2)
Nearest 1000=ROUND(A2,-3)

Here, are the result of these formulas.

This works only for the powers of tens: 10, 100, 1000. If you want to do it for 5 or 50, you have to take a different approach.

Rounding the number to the nearest 5, 10, 50, 100

Nearest 5=ROUND(A2/5,0)*5
Nearest 10=ROUND(A2/10,0)*10
Nearest 50=ROUND(A2/50,0)*50
Nearest 100=ROUND(A2/100,0)*100

You can use this simple formula to round a number to the nearest value.

Rounding number to the nearest value – MROUND

But there is another function you can use to achieve the same effect – MROUND. It gets two parameters: the number and the multiple.

Nearest 5=MROUND(A2,5)
Nearest 10=MROUND(A2,10)
Nearest 50=MROUND(A2,50)
Nearest 100=MROUND(A2,100)

These formulas will give us the same result as the previous ones.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.