Wrong calculation with MROUND function

...

Quote:

>Trouble is that many numbers are not capable of being exactly represented

in

>binary form. The result of this inexactitude is that the numbers that

MROUND

>is looking at are below 0.005 and rounded down instead of away from zero,

>which is the default for midway numbers.

...

>>I discovered a function in Excel 2000 as well as in Excel

>>97 that doesn't work properly. If you use MROUND function

>>to round a monetary value to a multiple of 0.05, you will

>>get different results:

>>Syntax: =MROUND(value;2)

>>Value = Result of MROUND function:

>>---------------------------------

>>0.425 = 0.45

>>1.425 = 1.45

>>2.425 = 2.4

>>3.425 = 3.4

...

It's definitely floating point rounding error. Also another clear indication

that Microsoft programmers responsible for the Analysis ToolPak just don't

understand floating point. The canonical way to perform rounding operations

is to scale the value, round to an integer, then rescale. In your case that

would be

=ROUND(value*20,0)/20 or =ROUND(value/0.05,0)*0.05

This algorithm has been around for as long as FORTRAN has. Even so, the

rocket scientists in Redmond have devised some other way to implement MROUND

that obviously fails to work in your case. So, don't use MROUND.

Replace MROUND(value,scale) with (ROUND(value/scale,0)*scale).

MROUND _should_ work the way the OP expects. That is doesn't has it's root

cause in the imprecision of floating point, but is proximately due to the

poor quality of MROUND's implementation.