How to "Round half to Even"?

Question

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

score 6 · Accepted Answer · edited Mar 18 '25 at 22:13

6

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.

edited Mar 18 '25 at 22:13

Giacomo1968

58,727

answered May 26 '16 at 09:41

Dominik

204

score 2 · Answer 2 · edited Mar 18 '25 at 22:13

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

score 1 · Answer 3 · edited Mar 18 '25 at 22:14

This VBA function worked for me in Excel 2024:


Public Function RoundHalfEven(ByVal num As Variant, ByVal numDigits As Integer) As Variant
    Dim factor As Variant
    Dim adjustedNum As Variant
    Dim roundedNum As Variant
    Dim roundedInt As Variant
' Use Decimal precision to avoid floating-point rounding issues
factor = CDec(10 ^ numDigits)
adjustedNum = CDec(num) * factor  ' Scale the number to an integer range

' Round to the nearest integer using VBA’s built-in rounding
roundedNum = CDec(Application.WorksheetFunction.Round(adjustedNum, 0))

' Check if the number is exactly halfway
If Abs(adjustedNum - roundedNum) = 0.5 Then
    roundedInt = CDec(roundedNum)

    ' If the rounded number is odd, adjust down to make it even
    If roundedInt Mod 2 &lt;&gt; 0 Then
        roundedNum = roundedNum - 1
    End If
End If

' Convert back to the original decimal scale
RoundHalfEven = roundedNum / factor

End Function

Usage:

RoundHalfEven(1.235, 2)=1.24
RoundHalfEven(1.245, 2)=1.24
RoundHalfEven(1.255, 2)=1.26

How to "Round half to Even"?

3 Answers3