General discussion

Locked

Excel: round currency to even cents

By kspugh ·
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?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel: round currency to even cents

by DKlippert In reply to Excel: round currency to ...

=EVEN()

Here's the MS take on rounding:

http://tinyurl.com/1se4

Collapse -

Excel: round currency to even cents

by DKlippert In reply to Excel: round currency to ...

http://www.j-walk.com/ss/excel/eee/eee011.txt

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

Collapse -

Excel: round currency to even cents

by kspugh In reply to Excel: round currency to ...

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

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums