# Lotus: Rounding numbers

Under ASTM rules, 1.35 and 1.45 both round to 1.4 since the “5” rounds to the even number (0 being treated as even).

Excel always rounds “5” up. This is causing problems since the results are not ASTM standard.

How does Lotus round numbers?

From Lotus Notes’ Help section:

Oops…submitted before I was finished typing…

I think Excel’s rounding issue comes from the fact that Excel does the math in binary format and then displays the number in decimal format.
If you still want to use Excel, though, I found this VBA function you can use to round numbers properly.

``````

Function XcRound(x As Double, n As Integer) As Double
'round x at a position n digits to the right of the
'decimal point (rounds to the nearest even)
'this function is anti-symmetric, so
'  MyRound(-x) = - MyRound(x)
Dim s As Double
Dim y As Double
Dim yi As Double
Dim yf As Double
Dim yiEven As Boolean

' handle x = 0 directly
If x = 0# Then
XcRound = x
Exit Function
End If

' if you get here then x is not 0

' work with y, the value of x scaled so the digit to
' be rounded-off is the first digit to the right
' of the decimal point
y = x * 10# ^ n

' strip off the sign of y
' this is done so the result is anti-symmetric
If y > 0# Then
s = 1#
Else
s = -1#
End If
y = Abs(y)

' decompose y into integer and fractional portions
yi = Int(y) ' the integer portion of y
yf = y - yi ' the fractional portion of y

' account for the difference between decimal and
' binary arithmetic as follows: if yf is near enough
' to 1/2 in a relative sense, then declare it to
' be exactly 1/2 (1/2 is exact in both decimal and
' binary)
' comment: double have about 14 decimal digits of
' resolution, so use the power 10 for a little
' comfort zone
If Abs((yf - 0.5) / y) < 10# ^ (-10) Then yf = 0.5

' determine if yi is even or odd
yiEven = ((yi Mod 2#) = 0#)

' decide how to treat the different cases:
' yf < 0.5, yf > 0.5, and yf = 0.5
If yf < 0.5 Then
yf = 0# ' round down
ElseIf yf > 0.5 Then
yf = 1# ' round up
Else
' here is where ties are broken
If yiEven Then
yf = 0#
Else
yf = 1#
End If
End If

' now put the pieces back together
XcRound = s * (yi + yf) / 10# ^ n
End Function

``````

Thanks

Er…what?

Rounding error from binary to decimal (all programs do math in binary, btw) is a different issue and not relevant here. Excel’s issues are caused by programmers who didn’t know the standards.

Sorry…as you can see from my last couple of posts, I was a little rushed when posting. I was thinking about the issues with floating point numbers, which is irrelevant to this specific rounding problem.