Wrong calculation with MROUND function
Author Message Wrong calculation with MROUND function

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
4.425 = 4.4
5.425 = 5.45
6.425 = 6.45
7.425 = 7.45
8.425 = 8.45
9.425 = 9.45
10.425 = 10.45
and so on...
508.425 = 508.45
but
1508.425 = 1508.40!

According to the rounding rule the result should always be
0.45, but as you see in the examples above, Excel rounds
sometimes the value down to 0.40. Doe's anybody know about
this problem?

Thanks for help!
Claudia

Sat, 06 Mar 2004 22:04:15 GMT  Wrong calculation with MROUND function
Hi Claudia!

I think that the problem is caused by floating point precision with binary
numbers.

http://www.microsoft.com/office/support/searchKB.htm

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.

hth

Quote:
> 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
> 4.425 = 4.4
> 5.425 = 5.45
> 6.425 = 6.45
> 7.425 = 7.45
> 8.425 = 8.45
> 9.425 = 9.45
> 10.425 = 10.45
> and so on...
> 508.425 = 508.45
> but
> 1508.425 = 1508.40!

> According to the rounding rule the result should always be
> 0.45, but as you see in the examples above, Excel rounds
> sometimes the value down to 0.40. Doe's anybody know about
> this problem?

> Thanks for help!
> Claudia

Sun, 07 Mar 2004 00:12:47 GMT  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.

Sat, 06 Mar 2004 21:56:53 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages