I needed to have a formula for Banker's Rounding and use it in Google Doc's Sheets app. This seems to work well. This only rounds to whole numbers, but could be modified to do currency pretty easily.
=if(mod(B2,0.5)>=0,IF(ISEVEN(B2),ROUNDDOWN(B2,0),ROUNDUP(B2,0)),round(B2,0))
I'd like to suggest a fix to this formula, as it rounds e.g. not only 1.5 to 2 and 0.5 to 0, but also 1.4 to 2 and 0.6 to 0, when Banker's rounding should work only on x.5 and other numbers should be rounded as they are, e.g. 1.4 to 1 and 0.6 to 1; ref. https://en.wikipedia.org/wiki/Rounding#Round_half_to_even and http://wiki.c2.com/?BankersRounding
ReplyDeleteI think this one should work better:
=IF(MOD(ROUND(B2, 1), 1)=0.5, IF(ISEVEN(B2),ROUNDDOWN(B2,0),ROUNDUP(B2,0)), ROUND(B2, 0))