Lotus: Rounding numbers

Factual Questions
1

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?

2

From Lotus Notes’ Help section:

3

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
4

Thanks

5

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.

6

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.