MROUND Rounding Issues (Rounding Down, Not Up)

Hi there,

I've been experiencing some unexpected behaviour when using the MROUND function.

I have a formula which rounds a dollar figure to the nearest x cents. For example I've been using

Formula:

Please Login or Register  to view this content.

to round x to the nearest 10 cents.

The formula works as expected most of the time, for example

Formula:

Please Login or Register  to view this content.

returns 205.3 and

Formula:

Please Login or Register  to view this content.

returns 85.9

The problem occurs when x is a multiple of 5 cents. In this situation I would expect Excel to round the value up, and the Microsoft support page for MROUND suggests that this is how the function should work

MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.

In some situations this works e.g.

Formula:

Please Login or Register  to view this content.

returns 6.2, however in others e.g.

Formula:

Please Login or Register  to view this content.

returns 26.1.

Any ideas what may be happening here? Any help would be greatly appreciated.

Thanks GC,

I wasn't aware of that, but it makes a lot of sense. I guess I'll have to look at other options.

It's interesting that despite the floating point issue, using

Formula:

Please Login or Register  to view this content.

returns the expected result.