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.

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.

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.

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

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

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

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

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