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

 

 

Excel MRound Function round to nearest multiple