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.

How do I modify rounding rules in Excel?
 

=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" wrote in message
...
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.

How do I modify rounding rules in Excel?
 

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