Hi, I need to apply the rounding rule below for conducting our business when
dealing with a number that has a 0.5 ending:

Example:
If the average is 54.5, I need excel to round it to 54.
If the average is 55.5, I need excel to round it to 56.

Please help! Thanks.

Hi,

Not clear about your question. Why should 5.5 be 54 and 55.5 be 56. If
55.5 is 56, then 54.5 should be 55?

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"riffmastr7" <(E-Mail Removed)> wrote in message
news:F196EB67-7813-487F-84A0-(E-Mail Removed)...
>
> Hi, I need to apply the rounding rule below for conducting our business
> when
> dealing with a number that has a 0.5 ending:
>
> Example:
> If the average is 54.5, I need excel to round it to 54.
> If the average is 55.5, I need excel to round it to 56.
>
> Please help! Thanks.

On Fri, 20 Feb 2009 13:50:01 -0800, riffmastr7
<(E-Mail Removed)> wrote:

>
>Hi, I need to apply the rounding rule below for conducting our business when
>dealing with a number that has a 0.5 ending:
>
>Example:
>If the average is 54.5, I need excel to round it to 54.
>If the average is 55.5, I need excel to round it to 56.
>
>Please help! Thanks.

It can be done easily with a UDF since VBA uses the so-called Banker's rounding
algorithm.

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use it, enter a formula like =RndToEven(num, Digits)

==========================
Function RndToEven(num As Double, Digits As Long) As Double
RndToEven = Round(num, Digits)
End Function
========================
--ron

Hi, Ashish. Please read my example very carefully. I need excel to round 54.5
to 54. This is called ASTM rounding rules. It only becomes tricky when there
is a 0.5 in the end. Basically, when there is a .5 in the end, the number is
rounded to the nearest EVEN WHOLE number. Here it is again:

Example:

If the average is 54.5, I need excel to round it to 54.
If the average is 55.5, I need excel to round it to 56.

"Ashish Mathur" wrote:

> Hi,
>
> Not clear about your question. Why should 5.5 be 54 and 55.5 be 56. If
> 55.5 is 56, then 54.5 should be 55?
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "riffmastr7" <(E-Mail Removed)> wrote in message
> news:F196EB67-7813-487F-84A0-(E-Mail Removed)...
> >
> > Hi, I need to apply the rounding rule below for conducting our business
> > when
> > dealing with a number that has a 0.5 ending:
> >
> > Example:
> > If the average is 54.5, I need excel to round it to 54.
> > If the average is 55.5, I need excel to round it to 56.
> >
> > Please help! Thanks.
>

Hi,

Sorry I did not get your question right. Please try this formula

IF(ISEVEN(INT(G7)),INT(G7),INT(G7)+1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"riffmastr7" <(E-Mail Removed)> wrote in message
news:473D231A-4619-47FA-A53E-(E-Mail Removed)...
> Hi, Ashish. Please read my example very carefully. I need excel to round
> 54.5
> to 54. This is called ASTM rounding rules. It only becomes tricky when
> there
> is a 0.5 in the end. Basically, when there is a .5 in the end, the number
> is
> rounded to the nearest EVEN WHOLE number. Here it is again:
>
> Example:
>
> If the average is 54.5, I need excel to round it to 54.
> If the average is 55.5, I need excel to round it to 56.
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> Not clear about your question. Why should 5.5 be 54 and 55.5 be 56. If
>> 55.5 is 56, then 54.5 should be 55?
>>
>> --
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "riffmastr7" <(E-Mail Removed)> wrote in message
>> news:F196EB67-7813-487F-84A0-(E-Mail Removed)...
>> >
>> > Hi, I need to apply the rounding rule below for conducting our business
>> > when
>> > dealing with a number that has a 0.5 ending:
>> >
>> > Example:
>> > If the average is 54.5, I need excel to round it to 54.
>> > If the average is 55.5, I need excel to round it to 56.
>> >
>> > Please help! Thanks.
>>

VBA's Round function is not as capable (unless improved in 2007) as the
worksheet ROUND function. In particular:
- VBA Round does not support Digits<0
- VBA Round is not well buffered against binary differences that have no
impact on the 15 decimal digit display, and so will sometimes produce
unintended results.

The VBA code I posted several years ago
http://groups.google.com/group/micro...7fce6145b70d69
addresses both of these issues.

Jerry

"Ron Rosenfeld" wrote:

> On Fri, 20 Feb 2009 13:50:01 -0800, riffmastr7
> <(E-Mail Removed)> wrote:
>
> >
> >Hi, I need to apply the rounding rule below for conducting our business when
> >dealing with a number that has a 0.5 ending:
> >
> >Example:
> >If the average is 54.5, I need excel to round it to 54.
> >If the average is 55.5, I need excel to round it to 56.
> >
> >Please help! Thanks.
>
> It can be done easily with a UDF since VBA uses the so-called Banker's rounding
> algorithm.
>
> To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
> highlighted in the Project Explorer window, then Insert/Module and paste the
> code below into the window that opens.
>
> To use it, enter a formula like =RndToEven(num, Digits)
>
> ==========================
> Function RndToEven(num As Double, Digits As Long) As Double
> RndToEven = Round(num, Digits)
> End Function
> ========================
> --ron

Hi,

As can be judged from my first response, I was not clear about what was
required and I still do not know what is ASTM. I really appreciate you
telling me that my answer is incorrect. From your second para, I understand
that the rounding rule has to be applied only when there is a .5. I could
not infer/read that in the original post. I though that all decimal numbers
had to be rounded off to the the closest even number.

Also, I have not tried your solution - may be it actually is the solution to
the question.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:(E-Mail Removed)...
> Again reluctant to argue with an MVP, Ashish, but for the second time this
> morning I'm not convinced by your answer. Have you had a long week? :-)
>
> If there isn't a .5 at the end, you should round to the nearest whole
> number. It is only when there is a .5 that you should look at odd or
> even.
> You are rounding 54.9 to 54 when it should be 54, and you are rounding
> 55.1 to 56 when it should be 55.
>
> Try =IF(OR(MOD(A2,1)<>0.5,ISODD(A2)),ROUND(A2,0),ROUNDDOWN(A2,0))
> --
> David Biddulph
>
> Ashish Mathur wrote:
>> Hi,
>>
>> Sorry I did not get your question right. Please try this formula
>>
>> IF(ISEVEN(INT(G7)),INT(G7),INT(G7)+1)
>>
>>
>> "riffmastr7" <(E-Mail Removed)> wrote in message
>> news:473D231A-4619-47FA-A53E-(E-Mail Removed)...
>>> Hi, Ashish. Please read my example very carefully. I need excel to
>>> round 54.5
>>> to 54. This is called ASTM rounding rules. It only becomes tricky
>>> when there
>>> is a 0.5 in the end. Basically, when there is a .5 in the end, the
>>> number is
>>> rounded to the nearest EVEN WHOLE number. Here it is again:
>>>
>>> Example:
>>>
>>> If the average is 54.5, I need excel to round it to 54.
>>> If the average is 55.5, I need excel to round it to 56.
>>>
>>> "Ashish Mathur" wrote:
>>>
>>>> Hi,
>>>>
>>>> Not clear about your question. Why should 5.5 be 54 and 55.5 be
>>>> 56. If 55.5 is 56, then 54.5 should be 55?
>>>>
>>>> --
>>>> Regards,
>>>>
>>>> Ashish Mathur
>>>> Microsoft Excel MVP
>>>> www.ashishmathur.com
>>>>
>>>> "riffmastr7" <(E-Mail Removed)> wrote in message
>>>> news:F196EB67-7813-487F-84A0-(E-Mail Removed)...
>>>>>
>>>>> Hi, I need to apply the rounding rule below for conducting our
>>>>> business when
>>>>> dealing with a number that has a 0.5 ending:
>>>>>
>>>>> Example:
>>>>> If the average is 54.5, I need excel to round it to 54.
>>>>> If the average is 55.5, I need excel to round it to 56.
>>>>>
>>>>> Please help! Thanks.
>
>

On Fri, 20 Feb 2009 21:00:01 -0800, Jerry W. Lewis <post_a_reply@no_e-mail.com>
wrote:

>VBA's Round function is not as capable (unless improved in 2007) as the
>worksheet ROUND function. In particular:
> - VBA Round does not support Digits<0
> - VBA Round is not well buffered against binary differences that have no
>impact on the 15 decimal digit display, and so will sometimes produce
>unintended results.
>
>The VBA code I posted several years ago
>http://groups.google.com/group/micro...7fce6145b70d69
>addresses both of these issues.
>
>Jerry

Thanks for posting that. And for the sake of the thread, here is Jerry's code:

==============================
Function ASTMround(number As Double, _
Optional num_digits As Integer = 0) As Double
' round exactly 5 to even per ASTM standard
' requires Excel 2000 or later
Dim x
If num_digits <= 0 Then
' VBA round does not accept num_digits < 0
ASTMround = Round(number / 10 ^ -num_digits) * 10 ^ -num_digits
Else
' buffer against binary approximations by rounding to an integer
' CDbl(CStr()) ensures that we get the primary binary _
representation the decimal display
ASTMround = Round(CDbl(CStr(number * 10 ^ num_digits))) _
/ 10 ^ num_digits
End If
End Function
=================================
--ron

ASTM is American Society for Testing and Materials. It is an international
standards organization that develops and publishes voluntary consensus
technical standards
http://www.astm.org/

Rounding (as opposed to truncation in the misleadingly named ROUNDDOWN and
ROUNDUP functions) is generally understood to mean replacing a number by the
nearest number with the rounded precision. This is only ambiguous in the
case of ties. The method I was taught in grade school handled ties by always
rounding them up (as the worksheet ROUND function does). One could as easily
round all ties down. Either way, this type of rounding introduces a bias,
since ties always round in a single direction.

That bias can be reduced/eliminated by handling ties in a way that rounds in
each direction roughly half of the time. The most common way to accomplish
this is to require that the last digit of the rounded number be even in the
case of ties.
http://en.wikipedia.org/wiki/Roundin...to-even_method
This approach has been in the literature for at least a century. It has
been an ASTM standard since 1940. It has been common practice in data
analysis at least since the 1940’s. Almost all standards bodies that bother
to specify how to round recommend it, and IEEE 754 specifies the binary
equivalent for hardware and software arithmetic. Microsoft calls this
"banker's rounding" for reasons that are mysterious to me, since finance is
about the only field where its use is not common.

Jerry

"Ashish Mathur" wrote:

> Hi,
>
> As can be judged from my first response, I was not clear about what was
> required and I still do not know what is ASTM. ...

Hi, David. Very good! This works! I want to take it one step further and make
it a calculation formula. I want to make the average of 4 numbers to follow
the same rounding rule.

Example: 57, 52, 52, 49

The average of the numbers above is 52.5. I want to put it in a single
formula to average them and come out as 52.

Please help.

"David Biddulph" wrote:

> Again reluctant to argue with an MVP, Ashish, but for the second time this
> morning I'm not convinced by your answer. Have you had a long week? :-)
>
> If there isn't a .5 at the end, you should round to the nearest whole
> number. It is only when there is a .5 that you should look at odd or even.
> You are rounding 54.9 to 54 when it should be 54, and you are rounding 55.1
> to 56 when it should be 55.
>
> Try =IF(OR(MOD(A2,1)<>0.5,ISODD(A2)),ROUND(A2,0),ROUNDDOWN(A2,0))
> --
> David Biddulph
>
> Ashish Mathur wrote:
> > Hi,
> >
> > Sorry I did not get your question right. Please try this formula
> >
> > IF(ISEVEN(INT(G7)),INT(G7),INT(G7)+1)
> >
> >
> > "riffmastr7" <(E-Mail Removed)> wrote in message
> > news:473D231A-4619-47FA-A53E-(E-Mail Removed)...
> >> Hi, Ashish. Please read my example very carefully. I need excel to
> >> round 54.5
> >> to 54. This is called ASTM rounding rules. It only becomes tricky
> >> when there
> >> is a 0.5 in the end. Basically, when there is a .5 in the end, the
> >> number is
> >> rounded to the nearest EVEN WHOLE number. Here it is again:
> >>
> >> Example:
> >>
> >> If the average is 54.5, I need excel to round it to 54.
> >> If the average is 55.5, I need excel to round it to 56.
> >>
> >> "Ashish Mathur" wrote:
> >>
> >>> Hi,
> >>>
> >>> Not clear about your question. Why should 5.5 be 54 and 55.5 be
> >>> 56. If 55.5 is 56, then 54.5 should be 55?
> >>>
> >>> --
> >>> Regards,
> >>>
> >>> Ashish Mathur
> >>> Microsoft Excel MVP
> >>> www.ashishmathur.com
> >>>
> >>> "riffmastr7" <(E-Mail Removed)> wrote in message
> >>> news:F196EB67-7813-487F-84A0-(E-Mail Removed)...
> >>>>
> >>>> Hi, I need to apply the rounding rule below for conducting our
> >>>> business when
> >>>> dealing with a number that has a 0.5 ending:
> >>>>
> >>>> Example:
> >>>> If the average is 54.5, I need excel to round it to 54.
> >>>> If the average is 55.5, I need excel to round it to 56.
> >>>>
> >>>> Please help! Thanks.
>
>
>