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

Máté Juhász
  • 21,403
  • 6
  • 54
  • 73
markzzz
  • 717
  • 3
  • 13
  • 22

2 Answers2

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.

Dominik
  • 194
  • 9
  • It gives to me #ERROR# : https://docs.google.com/spreadsheets/d/1YN5axU45_rGXSA3pN9j3zos3ArYx6jOmYcaatUzcJZk/edit#gid=0 – markzzz May 26 '16 at 09: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
1

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

Alexey Demin
  • 11
  • 1