Not understanding MROUND

N

nma2

Can somone answer an MROUND question for me?

Situation:

MROUND with a multiple of .1 rounds 773.65 to 773.6
MROUND with a multiple of .1 rounds 921.65 to 921.7

Isn't the remainder the same for both values? Shouldn't it round up
for both of these values?
 
B

Bernard Liengme

You have hit one problems associated with the way computers (not just Excel)
stores numbers. A decimal number like 773.65 must be convert to a binary
number. Sometimes an exact conversion is not possible. See these sites for
more detail
Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1
Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/defaul...pport/kb/articles/Q42/9/80.ASP&NoWebContent=1
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
Go to source>>
http://www.cpearson.com/excel/rounding.htm

If you type 773.65 in A1 and in B1 use =MROUND(A1,0.1) you get 773.6 (an
erroneous result)
But if in A2 you use =A1+0.0000000000001 then =MROUND(A2,0.1) gives the
correct value of 773.7. Adding that small amount overcame the binary
conversion problem. As you are interested in only once decimal place you
might consider using =MROUND(A1+0.0001, 0.1)

Note when I use correct/incorrect I assume we are not using the
'round-to-even' convention.

best wishes
 
Top