Rounding logic

[Swerly]

Hello, I am working with commissions and %'s and I have a rounding issue I cannot get "around" (pun intended)….

The formula I am working on is:
=45.90*15%
=6.885
Should = 6.88

=77.70*15%
=11.655
Should =11.66

I've used ROUND, CEILING, FLOOR and MROUND but no matter what combination, cannot get it to work.
One formula will work with one of them, but not the other.

 

[JimM]

6.885 to 6.88 is rounding down
11.655 to 11.66 is rounding up

What is the logic behind the two approaches?

 

[joeu2004]

Excel does "round half away from zero".

It appears that you want "round half to even", aka "banker's rounding".

The VB Round function does that. So you might enter the following function into a normal VBA module (not a worksheet object):

Function vbround(x As Double, p As Long) As Double
vbround = Round(x, p)
End Function

Caveat: VBA Round does not allow p<0.

There might be many Excel alternatives. IMHO, they are all fraught with risk due to 64-bit binary floating-point anomalies.

The following seems to work with positive values in A1:

=IF(AND(ISEVEN(INT(A1*100)), MOD(A1*100,1)=0.5), ROUNDDOWN(A1,2), ROUND(A1,2))