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?
=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 for any reason the other codes don't work. This is a brute force variant of the ISEVEN types of formulas.
=IF(AND(RIGHT(B29,1)="5", OR((RIGHT(LEFT(B29,LEN(B29)-1),1))="0", (RIGHT(LEFT(B29,LEN(B29)-1),1))="2", (RIGHT(LEFT(B29,LEN(B29)-1),1))="4", (RIGHT(LEFT(B29,LEN(B29)-1),1))="6", (RIGHT(LEFT(B29,LEN(B29)-1),1))="8")), ROUNDDOWN(B29,2), IF(AND(RIGHT(B29,1)="5", OR((RIGHT(LEFT(B29,LEN(B29)-1),1))="1", (RIGHT(LEFT(B29,LEN(B29)-1),1))="3", (RIGHT(LEFT(B29,LEN(B29)-1),1))="5", (RIGHT(LEFT(B29,LEN(B29)-1),1))="7", (RIGHT(LEFT(B29,LEN(B29)-1),1))="9")),ROUNDUP(B29,2), ROUND(B29,2)))