Banker's Round/Half to Even Rounding Double Rounding

I'm trying to set up Banker's rounding/Half to even. I found this formula from Barry Houdini

=ROUND(A2,0)-(MOD(ROUND(A2*10,0),20)=5)

Which works great, until there's a number with 4.45-4.49 in it.

34.44 rounds, correctly, to 34
34.45 rounds, incorrectly, to 33

Evaluating the formula shows that it's double rounding. A few steps in, I encounter the difference.

round(344.4,0) = 344
round(344.5,0) = 345

And this causes the MOD( result to change from 5 to 4. Which means it incorrectly subtracts 1 at the end.

Any suggestions on how to get passed that quirk? Thanks

Richard,
Thanks for the idea. That one seems to have it's own issues. It rounded 12.8 to 12 and not to 13.

Mr Shorty,
I've dabbled in some VBA, and tried yours out, but it seems to have an issue too. It rounded 12.51 to 13 and not 12. My formula is

=BankRound(A1,0)

MrShorty,
Banker's rounding is also called "half to even" we use for our chemistry is the same as the definition from wiki.
"A tie-breaking rule without positive/negative bias and without bias toward/away from zero is round half to even. By this convention, if the fractional part of x is 0.5, then y is the even integer nearest to x. Thus, for example, +23.5 becomes +24, as does +24.5; while −23.5 becomes −24, as does −24.5. This function minimizes the expected error when summing over rounded figures, even when the inputs are mostly positive or mostly negative."


12.4# rounds normally to 12
12.5# always rounds to 12, regardless of what numbers are after. It's only the number following the decimal that counts.
12.6# rounds normally to 13

So, Bo_Ry's also doesn't work either.

Bo_Ry,
That's perfect! It does just what I need. Now to see if I can decipher what you did so I can add it to my repertoire.