Rounding decimals function, not certain if it is correct

General
1

I’m writing a function to round double up to a certain number of decimals, but I’m not sure if it is correct for negative numbers. According to Wiki there are a number of ways to do it, but I’m not sure which one is commonly (most) used in accounting software.

My round method:

Function Round(number as Double, Decimals as Integer) As Double
  dim SignV as Integer = sign(number)
  dim tmpV as Double = number
  dim tmpVInt as UInt64
  tmpV = tmpV * signV
  tmpVInt = tmpV  * 10 ^ Decimals + 0.5
  return tmpVInt / 10 ^ Decimals * SignV
End Function

Test cases:

  dim Results(-1) as String
  Results.Append str(Round(3.78787,2)) ' 3rd decimal > 5
  Results.Append str(Round(3.78587,2)) ' 3rd decimal = 5
  Results.Append str(Round(3.78287,2)) ' 3rd decimal < 5
  
  Results.Append str(Round(-3.78787,2)) ' 3rd decimal > 5
  Results.Append str(Round(-3.78587,2)) ' 3rd decimal = 5
  Results.Append str(Round(-3.78287,2)) ' 3rd decimal < 5
  
  MsgBox "done"

Results:

3.79 3.79 3.78 -3.79 -3.79 -3.78

Someone who writes accounting software can confirm my function is correct?

Thanks!

2

If you’re writing accounting software, I would urge you to reconsider using floats or doubles. I don’t think there is any software dealing with currency that does any calculations using floats.
Using floats or doubles is asking for trouble if you need exactness. Not only for currency calculations but for everything where exactness is required. You will get unexpected results. Especially with rounding.

I suggest using Int’s or Int64’s internally by handling everything in cents or whatever precision you need.

There are a lot of topics out there. Here’s one: http://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency

3

There was a very interesting discussion about the best representation of decimals in accounting programs, as well as the rounding issues, plus workaround with Int64 at
https://forum.xojo.com/15425-is-currency-data-type-really-usable

That said, rounding rules seem OK to my limited experience as a shopkeeper, but once again, given the erratic behavior of double, I would not trust that type for something that requires precision up to three decimals as today’s tax rules require. I am sure there are people designing accounting programs here that can give a more accurate assessment, though.

4

Banker’s Rounding

"When you add rounded values together, always rounding .5 in the same direction results in a bias that grows with the more numbers you add together. One way to minimize the bias is with banker’s rounding.

Banker’s rounding rounds .5 up sometimes and down sometimes. The convention is to round to the nearest even number, so that both 1.5 and 2.5 round to 2, and 3.5 and 4.5 both round to 4. Banker’s rounding is symmetric. "

From

https://support.microsoft.com/en-us/kb/196652/en-us

5

@Jens Knaack Yes this is what I’m using, however I don’t find anything about negative numbers in that case:

Or: should -1.5 be rounded to -1 or -2?

6

There is no right or wrong; it’s a question of “definition”, what rounding of negative numbers is. As far as I know there no consensus.

@Alain Bailleul I don’t see Banker’s rounding as defined by Microsoft in the code. If you use this kind of rounding then -1.5 --> -2 and -4.5 --> -4.