Discussion Locked

Excel: round currency to even cents

0 helpful

3 replies

by kspugh Oct 3, 2002 last reply Oct 4, 2002

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need to round currency to the nearest even cent.
Can I use either the round or even paste functions & if so what is the syntax?

3 replies

=EVEN()

Here's the MS take on rounding:

POWER FORMULA TECHNIQUE

by Stephen Bullen

This formula perform 'bankers rounding' for a number (Num) to a given number
(Plc) of significant digits.

=MROUND(Num,IF(VALUE(RIGHT(Num/10^(INT(LOG(ABS(Num)))-Plc+1),2))=0.5,2,1)*
SIGN(Num)*10^(INT(LOG(ABS(Num)))-Plc+1))

If you define 'Fact' as =10^(INT(LOG(ABS(Num)))-Plc+1), this reduces to:

=MROUND(Num,IF(VALUE(RIGHT(Num/Fact,2))=0.5,2,1)*SIGN(Num)*Fact

******
This has also been suggested, but I haven't tried it.

Function RoundEven(num, Optional digits)
If IsMissing(digits) Then digits = 0
RoundEven = Round(CDbl(CStr(num)), digits)
End Function

Can you dissect this formula for me a bit? It is over my head.

This discussion is closed to new replies.