# Lotus: Rounding numbers

**URL:** <https://boards.straightdope.com/t/lotus-rounding-numbers/218144>\
**Category:** Factual Questions\
**Created:** [December 10, 2003, 2:29pm UTC](https://boards.straightdope.com/t/lotus-rounding-numbers/218144 "2003-12-10T14:29:40Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![starfish](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@starfish](https://boards.straightdope.com/u/starfish)\
**Post date:** [December 10, 2003, 2:29pm UTC](https://boards.straightdope.com/t/lotus-rounding-numbers/218144/1 "2003-12-10T14:29:40Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Starbury](https://avatars.discourse-cdn.com/v4/letter/s/f04885/32.png) [@Starbury](https://boards.straightdope.com/u/Starbury)\
**Post date:** [December 10, 2003, 2:37pm UTC](https://boards.straightdope.com/t/lotus-rounding-numbers/218144/2 "2003-12-10T14:37:15Z")

</div>

From Lotus Notes’ Help section:

> [@](#):
>
> If the first non-significant digit is 5, and all subsequent digits are  
> 0, the last significant digit is rounded to the nearest even digit.
> 
> For example:  
> Print Round(4.35, 1) ’ prints 4.4  
> Print Round(4.45, 1) ’ prints 4.4

---

<div class="post-metadata">

**Author:** ![Starbury](https://avatars.discourse-cdn.com/v4/letter/s/f04885/32.png) [@Starbury](https://boards.straightdope.com/u/Starbury)\
**Post date:** [December 10, 2003, 2:41pm UTC](https://boards.straightdope.com/t/lotus-rounding-numbers/218144/3 "2003-12-10T14:41:04Z")

</div>

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.

```auto

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

```

---

<div class="post-metadata">

**Author:** ![starfish](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@starfish](https://boards.straightdope.com/u/starfish)\
**Post date:** [December 10, 2003, 3:43pm UTC](https://boards.straightdope.com/t/lotus-rounding-numbers/218144/4 "2003-12-10T15:43:37Z")

</div>

Thanks

---

<div class="post-metadata">

**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\
**Post date:** [December 10, 2003, 7:47pm UTC](https://boards.straightdope.com/t/lotus-rounding-numbers/218144/5 "2003-12-10T19:47:00Z")

</div>

> [@](#):
>
> \*Originally posted by Starbury \*  
> \*\*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.  
> \*\*

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.

---

<div class="post-metadata">

**Author:** ![Starbury](https://avatars.discourse-cdn.com/v4/letter/s/f04885/32.png) [@Starbury](https://boards.straightdope.com/u/Starbury)\
**Post date:** [December 10, 2003, 8:56pm UTC](https://boards.straightdope.com/t/lotus-rounding-numbers/218144/6 "2003-12-10T20:56:38Z")

</div>

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.