MROUND Function

[Glenn Plummer]

Good Afternoon,

I am experiencing some problems using the MROUND
function in Excel 2000. For some apparent reason a few
of my numbers are not rounding off. Any assistance would
be greatly appreciated.


Thank You,

Glenn E. Plummer
Employee Insurance Program
LAN Administrator
(e-mail address removed)
803-734-0797

 

[Frank Kabel]

Hi
you may post your used formula aND the values together with the (wrong)
results

 

[Glenn Plummer]

-----Original Message-----
Hi
you may post your used formula aND the values together with the (wrong)
results

--
Regards
Frank Kabel
Frankfurt, Germany




.

Here are my results. Hope this helps you out.


Thanks,
=MROUND(C7,0.02)

1.27 1.28
2.27 2.28
3.27 3.28
4.27 4.26
5.27 5.28
6.27 6.28
7.27 7.26
8.27 8.26
9.27 9.26
10.27 10.28
11.27 11.28
12.27 12.28
13.27 13.28
14.27 14.28

 

[Frank Kabel]

[...]

Thanks,
=MROUND(C7,0.02)

1.27 1.28
2.27 2.28
3.27 3.28

Hi
try
=ROUNDUP(C7*50,0)/50

or
=CEILING(C7,0.02)

 

[Jerry W. Lewis]

This is not an Excel or an MROUND() issue per se; is is a well known
issue common to floating point calculations done in almost all computer
software and hardware.

0.02, 0.27, and most other decimal fractions have no exact
representation in binary. The best binary approximation you can make to
0.02 using the 8-bytes of IEEE double precision has a decimal value of

0.0200000000000000004163336342344337026588618755340576171875

Since this is larger than 0.02, there is the possibility (realized in a
few of your examples) that the binary approximation to n+0.27 will not
be large enough to round up when rounded to the nearest multiple of

0.0200000000000000004163336342344337026588618755340576171875

The best way to ensure that things round as you expected is to do
everything with integers (exactly representable in binary) and divide by
100 at the end. Since you probably don't want to re-enter your data as
integers, use
=MROUND(ROUND(x*100,0),2)/100
instead of
=MROUND(x,0.02)
The nested ROUND() is needed to ensure that you actually get an integer
from x*100, since x will usually only approximate the value you intended.

Jerry