Excel MRound Function round to nearest multiple

This tutorial explains how to use Excel MRound Function round to nearest multiple

Excel MRound Function round to nearest multiple

Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we don’t want the bonus amount looks better. For example, $11040 is rounded to $11000.

If you fail to use Excel MRound Function, navigate to Developer > Add-Ins > enable Analysis TooPak

Syntax of Excel MRound Function – round to nearest multiple

MROUND(number, multiple)
Number The value to round
Multiple Multiple to use in round, must be of the same sign as Number, otherise #NUM! will return

Note that the logic is same for positive and negative number. For example, Mround(0.5,1) = 1, MROUND(-0.5,-1) = -1

Example of Excel MRound Function – round to nearest multiple

Number =Round(cell,0) =Mround(cell,1) Explanation
0.1 0 0
0.2 0 0
0.3 0 0
0.4 0 0
0.5 1 1 Mround also round half up
0.6 1 1
0.7 1 1
0.8 1 1
0.9 1 1
1 1 1
-1.1 -1 #NUM! Multiple is positive but number is negative
-1.2 -1 #NUM!
-1.3 -1 #NUM!
-1.4 -1 #NUM!
-1.5 -2 #NUM!
-1.6 -2 #NUM!

Workaround of Excel MRound Function – #NUM! due to different sign

To avoid error due to sign, use the Sign Function to synchronize the sign of Multiple with Number. Sign Function is to return a positive value as 1, negative value as -1.

Number =Mround(cell,1) =Mround(cell,sign(cell)*1)
0.1 0 0
0.2 0 0
0.3 0 0
0.4 0 0
0.5 1 1
0.6 1 1
0.7 1 1
0.8 1 1
0.9 1 1
1 1 1
-1.1 #NUM! -1
-1.2 #NUM! -1
-1.3 #NUM! -1
-1.4 #NUM! -1
-1.5 #NUM! -2
-1.6 #NUM! -2

Outbound References

https://support.office.com/en-us/article/MROUND-function-C299C3B0-15A5-426D-AA4B-D2D5B3BAF427

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *