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

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

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

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

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),ROUND DOWN(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" wrote in message
...
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" wrote in message
...

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,

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
...
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),ROUND DOWN(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" wrote in message
...
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" wrote in message
...

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
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 1940s. 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),ROUND DOWN(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" wrote in message
...
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" wrote in message
...

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, Jerry. I appreciate the help. How do I use this in the spreadsheet. I put
it in VBA. How do I apply/ use this function? Please let me know. Also I want
to have a calculation (AVE) and this rounding rule all in one formula. Please
let me know how to do this.

Example: 57, 52, 52, 49

The average of these numbers is 52.5. I need excel to calculate the average
of these numbers and follow the ASTM rounding rule to give a result of 52
right away.

"Jerry W. Lewis" 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

"Ron Rosenfeld" wrote:

On Fri, 20 Feb 2009 13:50:01 -0800, riffmastr7
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

Why don't you just replace the A2 in my formula by your AVERAGE function?

=IF(OR(MOD(AVERAGE(57,52,52,49),1)<0.5,ISODD(AVER AGE(57,52,52,49))),ROUND(AVERAGE(57,52,52,49),0),R OUNDDOWN(AVERAGE(57,52,52,49),0))
--
David Biddulph

riffmastr7 wrote:
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),ROUND DOWN(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" wrote in message
...
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" wrote in
message ...

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 Sat, 21 Feb 2009 11:27:01 -0800, riffmastr7
wrote:

Hi, Jerry. I appreciate the help. How do I use this in the spreadsheet. I put
it in VBA. How do I apply/ use this function? Please let me know. Also I want
to have a calculation (AVE) and this rounding rule all in one formula. Please
let me know how to do this.

Example: 57, 52, 52, 49

The average of these numbers is 52.5. I need excel to calculate the average
of these numbers and follow the ASTM rounding rule to give a result of 52
right away.

If your values are in A1:A4, then:

=ASTMround(AVERAGE(A1:A4))

--ron

You are awesome, Ron!!!!! Thanks very much!! You are a life-saver!!! I really
appreciate it!

riffmastr7

"Ron Rosenfeld" wrote:

On Sat, 21 Feb 2009 11:27:01 -0800, riffmastr7
wrote:

Hi, Jerry. I appreciate the help. How do I use this in the spreadsheet. I put
it in VBA. How do I apply/ use this function? Please let me know. Also I want
to have a calculation (AVE) and this rounding rule all in one formula. Please
let me know how to do this.

Example: 57, 52, 52, 49

The average of these numbers is 52.5. I need excel to calculate the average
of these numbers and follow the ASTM rounding rule to give a result of 52
right away.

If your values are in A1:A4, then:

=ASTMround(AVERAGE(A1:A4))

--ron

Thanks so much, David!!! This works very well. I really appreciate it. You
are the man!!!
riffmasrt7

"David Biddulph" wrote:

Why don't you just replace the A2 in my formula by your AVERAGE function?

=IF(OR(MOD(AVERAGE(57,52,52,49),1)<0.5,ISODD(AVER AGE(57,52,52,49))),ROUND(AVERAGE(57,52,52,49),0),R OUNDDOWN(AVERAGE(57,52,52,49),0))
--
David Biddulph

riffmastr7 wrote:
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),ROUND DOWN(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" wrote in message
...
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" wrote in
message ...

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.