Rounding Help Please

[C.L.A (PTY) Ltd]

I'm currently sitting with a predicament, because I received a rounding
procedure which excel doesn't cater for. The reports require a rounding
procedure that isn't roundup, roundown, or round.

An Example:

If the digit following the last significant figure is a 5, the number is
rounded to the nearest even digit.

eg. 10.65 is rounded to 10.6
10.75 is rounded to 10.8
10.55 is rounded to 10.6
10.45 is rounded to 10.4

This only applies when the last digit is a 5, if any digit follows the 5,
the number is rounded up to the next digit.

eg. 10.651 is rounded to 10.7
10.451 is rounded to 1.5

It all sounds simple, but it is definately not.

Any help will be appreciated

Some References:
1. Analytical Chemistry - Gary D Christian, Chapter 2 Data handling
2. Chemistry - Michael Sienko and Robert Plane, Chapter 1.3 Management
3. Fundamentals of analytical chemistry -Skoog, West and Holler, Chapter
2 Errors in chemical analysis

Kindest regards,

Johan Els

[Jerry W. Lewis]

This method of rounding is specified by ASTM, and most other standards
bodies that choose to define how rounding is to be done. Excel 2000
(and all later versions) added a Round() function in VBA that attempts
to implement this method. You could write a VBA function that you would
call from Excel that would use the VBA round function.

The difficulty in programming any rounding procedure (round 5 up, round
5 to even, etc.) is that it is based on properties of the decimal
representation of the number, while computers represent numbers in
binary. Most exact decimal fractions have no finite exact binary
representation. For instance the binary approximations to 0.05, 0.45,
0.55, and 0.65 are slightly greater than their nominal decimal values,
while the binary approximations to 0.15, 0.35, 0.85 and 0.95 are
slightly smaller than their nominal decimal values. The point is that
the key condition of whether the first digit to round away is <5, =5, or
>5 cannot be reliably determined beyond the first decimal place. If
you try to roll your own, probably the best you can do is ask whether
the part to be rounded away is within epsilon of 5, and if so, then
presume that it is exactly 5.

I have only done limited testing on the following code, but it seems to
work well without re-inventing the wheel,

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

and even handles known problem values well like

http://groups.google.com/groups?selm=408877F2.5010601%40no_e-mail.com

Jerry