Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what are the rules for a valid sheet name in excel | Excel Discussion (Misc queries) | |||
Copying Rules in Excel | Excel Discussion (Misc queries) | |||
how do I set up a validation rules with two data rules | Excel Worksheet Functions | |||
How can I alter the rounding rules? | Excel Worksheet Functions | |||
I need 5 conditional formatting rules-excel only allows 3 | Excel Discussion (Misc queries) |