I need to "Round half to Even" (Bankers' rounding) a number in Excel. So:
4961,25 => 4961
9738,75 => 9739
4934,5 => 4934
4935,5 => 4936
How can I do it? It must also work "online", using Google Sheets (here is a ready sheet).
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI need to "Round half to Even" (Bankers' rounding) a number in Excel. So:
4961,25 => 4961
9738,75 => 9739
4934,5 => 4934
4935,5 => 4936
How can I do it? It must also work "online", using Google Sheets (here is a ready sheet).
This formula implements the equation from Wikipedia:
= -CEILING(-a_value - 0.5, 1) - 1 + ABS(SIGN(MOD(a_value, 2) - 0.5))
It works in Excel and Google Sheets. The spaces are optional.
= -CEILING(-a_value - 0.5, 1) - 1 + ABS(SIGN(MOD(a_value, 2) - 0.5))
Sometimes does't works because even if you see 0.00 value, in fact it could be something like 8,88E-16 so SIGN() function return non zero value and CEILING() works not correct.
This one solve the problem:
= -CEILING(-round(value,12) - 0.5, 1) - 1 + ABS(SIGN(MOD(round(value,12), 2) - 0.5))
And here is ROUND_HALF_EVEN(value, 2) implementation:
= -CEILING(-round(value,12) - 0.005, 0.01) - 0.01 + ABS(SIGN(MOD(round(value*100,12), 2) - 0.5))*0.01