How to Round a Number to Closest ten in Excel

Rounding a number in Excel is very easy. There are situation wherein we need to round a number to closest 10 or 100. For example, if we have a number 115 then 120, if we have 112 then 110. For this there is a dedicated function in Excel called MROUND which comes useful for such scenario. Let us learn more about the function MROUND in Excel.

Syntax of Excel MROUND function

MROUND(number, multiple)

Where,

• Number Required. The value to round.
• Multiple Required. The multiple to which you want to round number.

Note: MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.

Example:

Below are the few useful examples of MROUND in Excel.

1. Rounding a number to multiples of 10

=MROUND(112, 10) Rounds 112 to the nearest multiple of 10 which will return 110

=MROUND(116, 10) Rounds 116 to the nearest multiple of 10 which will return 120

2. Rounding a number to multiples of 5

=MROUND(112, 5) Rounds 112 to the nearest multiple of 5 which will return 110.

=MROUND(116, 5) Rounds 116 to the nearest multiple of 5 which will return 115.

=MROUND(118, 5) Rounds 118 to the nearest multiple of 5 which will return 120.

There are other two Excel function which are very similar to this function but with slight change on the functionality.

1. CEILING(number,significance)

This will Returns number rounded up, away from zero, to the nearest multiple of significance. For example

CEILING(112,5) will return 115

CEILING(112,10) will return 120

CEILING(115,5) will return 120.

2.FLOOR(number,significance)

This will Rounds number down, toward zero, to the nearest multiple of significance.  For example,

FLOOR(112,5) will return 110

CEILING(115,10) will return 110.

So in summary, MROUND Excel function is useful when we need to round number up or down to the nearest multiple. Whereas if you want roundup decimals then ROUNDUP function is useful. For rounding down decimal ROUNDDOWN excel function is useful. Depending on the requirements, these Excel functions will help us to save our time.

1 comment… add one
• Pramod Mahor Apr 30, 2018, 10:13 am

GOOD INFORMATION NICE ARTICLE
THANKS