8

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).

5

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.

2
  • It gives to me #ERROR# : docs.google.com/spreadsheets/d/…
    – markzzz
    May 26 '16 at 9:52
  • 2
    I see this is due to the locale settings (Italian?) of your Google Sheet. Replace periods with commas and commas with semicolons in the formula above. I copied the adapted formula to your sheet.
    – Dominik
    May 26 '16 at 10:00
0

= -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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.