New Post has been published on Access-Excel.Tips
New Post has been published on http://access-excel.tips/excel-mround-function-multiple/
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
