How do I modify rounding rules in Excel?

I would like to have excel use a modified rounding rule, where when a
calculation anwers has a '.5' value the odd numbers rounded up to next whole
number and even numbers are rounded down.....(example 13.5 would be rounded
up to 14 and 4.5 would be rounded down to 4). I need to have ecel do a
computation and then apply this rounding rule.

=IF(MOD(A1, 1)=0.5, INT(A1) + MOD(INT(A1), 2), ROUND(A1, 0))

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"merritaf" <[email protected]> wrote in message
news:[email protected]...
>I would like to have excel use a modified rounding rule, where when a
> calculation anwers has a '.5' value the odd numbers rounded up to next
> whole
> number and even numbers are rounded down.....(example 13.5 would be
> rounded
> up to 14 and 4.5 would be rounded down to 4). I need to have ecel do a
> computation and then apply this rounding rule.

If you are interested in a more general solution (rather than just rounding to
an integer), you can take advantage of the fact that VBA's Round function
works this way. This is sometimes referred to as 'Banker's rounding', as it
eliminates bias (half the time the .5 is rounded up, half the time down).

The following VBA function will apply those rounding rules. The code is a bit
more complicated than you might expect, since Excel's ROUND worksheet function
accommodates a negative number of decimal places and and VBA doesn't, and I
wanted them to work the same other than the handling of the 0.5 issue.

Function BankersRound(Number As Double, Places As Long) As Double
Dim X As Double

If Places < 0 Then
X = 10 ^ Places
BankersRound = Round(Number * X, 0) / X
Else
BankersRound = Round(Number, Places)
End If
End Function


On Wed, 26 Jan 2005 12:17:03 -0800, "merritaf"
<[email protected]> wrote:

>I would like to have excel use a modified rounding rule, where when a
>calculation anwers has a '.5' value the odd numbers rounded up to next whole
>number and even numbers are rounded down.....(example 13.5 would be rounded
>up to 14 and 4.5 would be rounded down to 4). I need to have ecel do a
>computation and then apply this rounding rule.