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.