Menu
It is a well known problem that by default, .NET uses Banker's rounding whereby X.5 is rounded to nearest even number. Excel, on the other hand uses arithmetic rounding where X.5 is always rounded up, just like most of us have been taught at school.
Is it possible to perform banker's rounding in Excel using a formula?
363
What is round to even in Excel? Round to even also called bankers rounding, basically it is just like normal rounding, except when the number to be rounded on is number 5. If the digit before the 5 is odd, you should round up, and if the digit before the 5 is even, you should round down.
It's a commonly used method for rounding taxes. Rather than rounding 0.5 and higher up, and 0.4 and lower down, bankers rounding rounds 0.5 to the nearest even number. Essentially if the cent value of the tax total is odd, the 0.5 (half-cent) rounds upwards; If the cent value is even, the half-cent rounds it downwards.
For example, to round 2345678 down to 3 significant digits, you use the ROUNDDOWN function with the parameter -4, as follows: = ROUNDDOWN(2345678,-4). This rounds the number down to 2340000, with the "234" portion as the significant digits.
Use this formula:
=IF(AND(ISEVEN(A1*10^0),MOD(A1*10^0,1)<=0.5),ROUNDDOWN(A1,0),ROUND(A1,0))
Replace all the 0, there are 4 of them, with the significance of the desired rounding.
Or you can create a user defined function to use VBA Round, which is Banker's Rounding:
Function BankerRound(rng As Double, sig As Integer) As Double
BankerRound = Round(rng, sig)
End Function
Then it would simply be:
=BankerRound(A1,0)
=IF(AND(ISEVEN(A1*10^0),MOD(A1*10^0,1)<=0.5),ROUNDDOWN(A1,0),ROUND(A1,0))
I used this formula and it worked great, but in some cases, it was not working because the 0.5 actually was 0.5000000000000000004550000000000000000.
The fix is to ROUND the MOD statement to two(2) decimal places so that the computation reads:
=IF(AND(ISEVEN(A1*10^0),ROUND(MOD(A1*10^0,1),2)<=0.5),ROUNDDOWN(A1,0),ROUND(A1,0))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
