Bankers Rounding HELP

[Tufty]

I’ve seen lots of stuff about bankers rounding which just lead me down a rabbit hole I really didn’t want to go down.
All I want is to figure out either a good formula that does ‘Bankers Rounding’ or ‘Half Way Even Rounding’

It seems like such an easy thing to do on paper, the rules are really clear, but I can’t for the life of me find a reliable formula to do it without getting into what for me is incredibly high level mathematics that I’m just not able to understand at all.

I’ve tried a few formulas already and for some unknown reason I can’t get them to work as expected.

I’m trying to round decimal currency, so for example 1.755 would round UP to 1.76 and 1.765 would round DOWN to 1.76 as in both cases 1.7(6) is the nearest EVEN digit.

Does anyone here know of a fairly simple formula that I can use for this?

I found a spreadsheet online that had a VBA formula, but it was just totally beyond me how I could import that into my libreoffice calc system. They always assume you know much more about what you’re doing than you actually do, so I ended up faced with loads of different macro VBA editing formatting inserting windows that had multiple possible choices, none of which made any sense to me at all. So I just gave up on that route!

[Villeroy]

BankersRounding.ods (29.6 KB)

[sokol92]

If you are satisfied with 6 decimal places of accuracy (usually this is the case in financial calculations), then for banking rounding to 2 decimal places you can use the formula (check it out!):

=ROUND(A2; 2)-IF(AND(RIGHT(FIXED(A2;6;1);4)="5000"; ISEVEN(VALUE(LEFT(RIGHT(FIXED(A2;6;1);5);1))));1/100;0)

[Tufty]

Thank you so much sokol92 !
That one works better than any I’ve seen.
6 decimal places is fine as I’m only working with decimal currency values.
I think converting things to text strings as you’ve done is what makes the difference.

Thanks Again.

[karolus]

Hallo

=MROUND( A1 ; 0.02 )
edit:

=IF(MOD(A2;0.02)=0.005;ROUNDDOWN(A2;2);ROUND(A2;2))

[mikekaganski]

Bankers rounding

Actually it’s not easy to implement. MROUND as suggested above would round any number to an even number; while banker’s rounding is only applied to the specific case of rounding exact half (x.5) to even neighbor (thus it rounds x.5 down in half cases, and up in the other half cases). See [libreoffice-users] Missing function: Bankers Rounding – The Document Foundation Mailing List Archives

@Tufty: no formula in Calc may help you, except - using macros, specifically - VBA’s Round function in your user-defined spreadsheet function. In low-level programming languages, one may choose the rounding mode; in Calc, you can’t (and also, you can’t in Basic - but there, you may use pre-defined functions).