How do I apply a rounding rule

[riffmastr7]

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.

 

[Ashish Mathur]

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

 

[Ron Rosenfeld]

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

 

[riffmastr7]

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]

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

 

[Jerry W. Lewis]

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/microsoft.public.excel.charting/msg/107fce6145b70d69
addresses both of these issues.

Jerry

 

[Ashish Mathur]

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

 

[Ron Rosenfeld]

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/microsoft.public.excel.charting/msg/107fce6145b70d69
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

 

[Jerry W. Lewis]

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/Rounding#Round-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

 

[riffmastr7]

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.

 

[riffmastr7]

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.

 

[Ron Rosenfeld]

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

 

[riffmastr7]

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

riffmastr7

 

[riffmastr7]

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