Round() is broken

[TheMario]

Maybe I just misunderstand the principles of math, but when I round any of the following numbers 4.185, 4.145, 4.225, 4.265 to the hundredths place, the numbers are rounded down instead of up.

For example:

Round (4.185, 2) -> 4.18

Round (4.145, 2) -> 4.14

Round (4.225, 2) -> 4.22

Round (4.265, 2) -> 4.26

What makes this so bad is that it doesn't happen in every case of a trailing 5. In other words, 4.175 and just about any number in between the ones mentioned above (I did not test them all) round up as they should.

I guess my question is: why does this happen? Am I missing something, or has anyone else noticed this? Many of my reports were often a penny off, and I thought I was doing something wrong. Now I see that the function appears to be bugged.

Any suggestions? I have already written a function to test for the trailing 5 and add .001 to it in order to force the round up instead of down. Since I am working with money this works for me, but what about folks who are potentially rounding to other decimal places, they'd have to write their own round function altogether I suppose.

Oh, and hi I'm TheMario, Nice to meet ya.

[Richard Robertson]

It is most likely due to the fact that floating point decimal numbers are inaccurate. It's possible that your 4.185 is actually a 4.18499999999 on the hardware.

Edited February 18, 2010 by Richard Robertson

[Kealper]

Hmm...well that is quite strange, I always figured if you made a float 1.445, it would be exactly 1.445...here is what I got from my computer the comments are "what it should be" -> "what it was rounded to"

ConsoleWrite(Round(4.185,2) & @CRLF)    ;4.19 -> 4.18
ConsoleWrite(Round(5.345,2) & @CRLF)    ;5.35 -> 5.35
ConsoleWrite(Round(12.435,2) & @CRLF)   ;12.44 -> 12.44
ConsoleWrite(Round(10.005,2) & @CRLF)   ;10.01 -> 10.01
ConsoleWrite(Round(9.105,2) & @CRLF)    ;9.11 -> 9.11
ConsoleWrite(Round(5.785,2) & @CRLF)    ;5.79 -> 5.79
ConsoleWrite(Round(1.115,2) & @CRLF)    ;1.12 -> 1.12

That gives me an idea, I'll run it on all my computers and see if it gets 4/7 of them correct

Edited February 18, 2010 by Kealper

[jchd]

That's the problem with binary FP. The precision isn't guaranteed and you need to take more or less complex steps to protect against its loss. The examples show that even the simple representation of innocent-looking values is by itself already a problem. Imagine what is a complex scientific application with sometimes billions of FP operations per second until a result shows. You really have to know how to monitor precision loss while coding the computations, else you're likely to end up with a completely meaningless result.

This is no more the case with decimal FP, like IEEE754R, but too few applications use it. That siuation is somehow paradoxal: outside of actual scientific activities, most FP is used by business apps, for which FP accuracy is paramount. At the same time, very few of these business apps use anything else than inaccurate binary FP. Go figure!

@TheMario:

What I do in your situation is that I handle only integral quantities: amounts in cents (not fractional $ or €), quantities in smallest fractional units, etc. I regard the decimal dot (in business apps, again) and thousand separator as a presentation / display format, not a storage or computation format. That simplifies design and coding a lot and makes your data more locale-independant. The same remarks apply to dates, times, timestamps as well: only ISO format makes sense or well chosen epoch. Once you stick to these simple rules, everything becomes easy and robust. When it comes to display, then it's a simple matter to use StringFormat or whatever function you need: it doesn't matter if the output routine takes time as it isn't a bottleneck for either display or even printing, which are slow operations. OTOH, internal computations are as fast and easy as possible. Win-Win situation.

Edited February 18, 2010 by jchd

[TheMario]

@TheMario:

What I do in your situation is that I handle only integral quantities: amounts in cents (not fractional $ or €), quantities in smallest fractional units, etc. I regard the decimal dot (in business apps, again) and thousand separator as a presentation / display format, not a storage or computation format. That simplifies design and coding a lot and makes your data more locale-independant. The same remarks apply to dates, times, timestamps as well: only ISO format makes sense or well chosen epoch. Once you stick to these simple rules, everything becomes easy and robust. When it comes to display, then it's a simple matter to use StringFormat or whatever function you need: it doesn't matter if the output routine takes time as it isn't a bottleneck for either display or even printing, which are slow operations. OTOH, internal computations are as fast and easy as possible. Win-Win situation.

Well the problem is that the database I am pulling the data from stores sales tax as an exact figure (in other words it does not just store the real world value). It would probably be more difficult for me to write a routine to handle "string rounding", though in another case your suggestion may have worked well.

I did actually come up with a solution (wrote a function that adds .001 to a value if it has the trailing .005, and then round after that), but I was still curious as to why this is happening, and if it was just happening to me because I picked on that red headed girl in the 3rd grade.

Anyways, thanks for the input, everyone.

TheMario.

[andybiochem]

This is a very interesting thread!

I was aware of problems with FP but didn't think they'd manifest in this way. I'll probably write my own Round function as well as a result of this thread.

What I do in your situation is that I handle only integral quantities...

@jchd - would you mind elaborating on how you go about this on a practical level. e.g. do you work with small numbers several orders above their 'display' value e.g. 1.2345 would be worked as 12345 ? If so, how do you keep tabs on the lowest level of precision?

I'm very tempted to work numbers exclusively as strings as in the case of the BigNum.au3 UDF. That way, we don't lose out to fp imprecision.

[jchd]

I work with the lowest decimal rank needed for the application, assuming we still talk about business applications, of course. I'm also doing works in number theory and other maths/scientific areas where unbounded precision is often required, but I'd say this is then more a job for specialized packages like Mathematica, Sage or GP/Pari than for AutoIt (for obvious reasons).

Given that almost everywhere we can work with 64-bit integers or rather 63-bit signed integers, saturation of this range even in 1/1000 of the weakest currency on Earth is enough to turn you into a multi-billionaire. Indeed 2^63 - 1 = 9223372036854775807 ≈ 9.2 10¹⁸ . Even when talking in units of 1/1000 of the weakest currency, say 1/1000 US$, still leaves you play with 9.2 10¹² US$, and I believe that when any gross total in a business is flirting anywhere close to this bound you deserve having a staff of seasonned professionals taking care of this for you.

I kown you're doing work in a lab and we know it's the kind of place where you could have to process µg or less, ns or less, I mean quantities that "look" very small. But I find it hard to imagine a single application having to deal on the same data with ultra-small and ultra-large quantities so as to saturate a 10¹⁸ range in whatever measure (time, weight, size, ...). Whenever you need a wider span, then it's always time to switch to FP with its share of drawbacks, but you don't determine the diameter of our galaxy in fm (femtomètre). An alternative is to use a log scale mapped to an integer range using a suited scale factor.

Edit: remove spurious "link" that got paste there ???

Edited February 22, 2010 by jchd

[PartyPooper]

Maybe I just misunderstand the principles of math, but when I round any of the following numbers 4.185, 4.145, 4.225, 4.265 to the hundredths place, the numbers are rounded down instead of up.

I was always taught <5 round down, =>5 round up.

This pretty much sums up (no pun intended) what I've been taught: Rounding Numbers

[jchd]

Read the post again, that's precisely the point we're discussing about!

[jchd]

Well the problem is that the database I am pulling the data from stores sales tax as an exact figure (in other words it does not just store the real world value). It would probably be more difficult for me to write a routine to handle "string rounding", though in another case your suggestion may have worked well.

I see, you're getting a more or less already truncated string representation of a FP that you need to adjust to the real world.

The problem here is that even seemingly "easy" cases are ... not! For instance, the FAQ of SQLite gives an enlightning example:

"Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?

SQLite uses binary arithmetic and in binary, there is no way to write 9.95 in a finite number of bits. The closest to you can get to 9.95 in a 64-bit IEEE float (which is what SQLite uses) is 9.949999999999999289457264239899814128875732421875. So when you type "9.95", SQLite really understands the number to be the much longer value shown above. And that value rounds down."

Your examples in the first post

Round (4.185, 2) -> 4.18

Round (4.145, 2) -> 4.14

Round (4.225, 2) -> 4.22

Round (4.265, 2) -> 4.26

show that going to FP may in fact make things worse as the final 5 in your input data (still assuming it's a string) gets into the FP mixer ... and get mixed down on occasion.

Now suppose you get values from the DB having 3 decimals as strings. Now simply remove the dot, leaving you a value expressed in 1/000 <unit> (<unit> can be $ or whatever). That can be made an integer with Number or an implicit conversion at little cost. Rounding to 1/100 <unit> as you were trying to do means adding 5, divide by 10 and cast to integer again.

OTOH, you may get the same result adding 0.0051 to the input FP value and truncate after (not round again !). A similar "workaround" was in effect in some U*ix like system some time ago, but was finally discarded AFAIK.

When I ear or read pejorative stances about the good old Cobol and its so-called bullshit BCD representation, I sometimes wish every language would have similar bullshit.

[andybiochem]

This is no more the case with decimal FP, like IEEE754R, but too few applications use it.

I've not had chance to look into this yet, is this something other languages might use? Or is it a programmer-level approach? I've been toying with the idea of learning Fortran just for faster number work. Is there a language that I could lear to use along side AI which is designed specifically for maths? I read a book a while back called "The Search For Omega" which referenced Lisp quite alot, but seemed quite antiquated.

[jchd]

Here's what people at Intel say about full hardware implementation. In short: not for tomorrow albeit some high-end server IBM processors implement it (see comments on the cited page). A software implementation is readily available at the same place. I downloaded it and it should be relatively easy to build it and make a wrapper for AutoIt. The Intel lib offers 64- and 128-bit types and operations for decimal FP. The number of significant decimal digits are resp. 16 and 34, with range resp. 1.0 10^-383 .. 9.9 10³⁸⁴ and the impressive 1.0 10^-6143 .. 9.9 10⁶¹⁴⁴. IMHO it needs to be decided which to implement to minimize bloat. Given that AutoIt isn't aimed at intensive scientific computations it could be a nice add-on to have and would certainly solve all FP issues. Of course, not having built-in operators is a small nuisance due to the core not being OO, but I don't see that as blocking.

As a sidenote, a C revision is underway to support decimal FP in the standard and there are certainly efforts to do the same for many other languages as well.

To ease your search for a suitable programming language, I invite you to dig here or if you prefer there for something more visual. In fact providing you with the two links may confuse you even more

[andybiochem]

Now suppose you get values from the DB having 3 decimals as strings. Now simply remove the dot, leaving you a value expressed in 1/000 <unit> (<unit> can be $ or whatever). That can be made an integer with Number or an implicit conversion at little cost. Rounding to 1/100 <unit> as you were trying to do means adding 5, divide by 10 and cast to integer again.

This is what I quickly came up with and seems to work with all the numbers I've tested.

ConsoleWrite(Round(4.265,2) & @CRLF)
ConsoleWrite(_Round(4.265,2) & @CRLF)


Func _Round($nNumber, $iDP = 3)

    ;- get factor to put number into integer range -
    $iFactor = 10 ^ ($iDP + 2)
    ;- put number into integer range -
    $nNumber *= $iFactor
    ;- un-float number -
    $nNumber = Int($nNumber)
    ;- check last digit is 0 -
    If StringRight($nNumber,1) >= 5 Then $nNumber += 5
    ;- back-round to tens -
    $nNumber = Round($nNumber,-2)
    ;- put number back to float -
    $nNumber /= $iFactor

    Return $nNumber

EndFunc

Are floats always inaccurate in the negative direction? e.g. can 1.234 be held as 1.23400000000001 on hardware? or will it always tend to be smaller on the hardware e.g. 1.233999999999 etc?

I'm trying to account for a creeping imprecison (checking for 5 rather than 9), but other than that can you see a situation where the above code may fail?

[jchd]

I ain't a FP guru, but AFAIK negatives are just positives with a minus sign. The conversion is always the same, numerically, so I believe you an expect +1/3 and -1/3 o have the same imprecision in absolute value (hopefully). Of course _you_ need to take the sign into account, i.e. change your signs accordingly when your round "by hand".

For instance the += 5 part has to be += 5 * Sign($nNumber).

I wish AutoIt would have a built-in Sign() function.

[andybiochem]

Of course _you_ need to take the sign into account, i.e. change your signs accordingly when your round "by hand".

For instance the += 5 part has to be += 5 * Sign($nNumber).

I guess this would change depending on the type of rounding we want?

I've always been of the "half away from zero" school, so that is what happens in my code. i.e.

_Round(-1.2345,3) = -1.235

This might not be the same for other types of rounding (asymetric, gaussian, stochastic, etc)

I might expand my function to include these, but I think Mat has already approached this.

Cheers jchd!

[jchd]

Of course you're right. But sincerely, this is the most common use for layman rounding and AI is far enough from a scientific package so that there's little hesitation.

Ha yes, there is a fairly common exception for a particular set of rules: eBay rounding. <-- private joke for those who have at least once looked at what eBay calls an "invoice"...

[andybiochem]

Of course you're right. But sincerely, this is the most common use for layman rounding and AI is far enough from a scientific package so that there's little hesitation.

Cool, I'll default the function to the layman 'round half up', with options for other styles.

[andybiochem]

Damn! Found a fault.

_Round(1.14999,1) = 1.2

Having thought about this a bit more, I'm not sure if it is possible to resolve the fp issue without getting the user to implicitly declare how many digits should be in the number.

In the example 1.14999, my function assumes the existence of trailing 9's as evidence of floating point error in the number 1.15

Without the user stating 'there should be 3 digits in the number', my function could over-correct some numbers.

This is turning into a bit of a headache for me even with my simple use of numbers, I can't imagine what it must be like for a dev trying to write a language that has to deal with this sort of thing internally!

I think I'm going to have to thoroughly re-think how to approach my programming.

[EDIT]

Thinking out-loud: this seems to work.....

ConsoleWrite(_Round(1.149999,1) & @CRLF)

Func _Round($nNumber, $iDP = 3)

    ;- get factor to put number into integer range -
    $iFactor = 10 ^ ($iDP + 1)
    ;- put number into integer range -
    $nNumber *= $iFactor
    ;- un-float number -
    $nNumber = Int($nNumber)
    ;- back-round to units -
    $nNumber = Round($nNumber,-1)
    ;- put number back to float -
    $nNumber /= $iFactor

    Return $nNumber

EndFunc

Edited February 26, 2010 by andybiochem

[jchd]

You have it: it's no difficult to come up with a seemingly innocent decimal representation that will behave unexpectedly at the binary (FP) level. Let's take the decimal value 0.1 as example: it's (infinite periodic) binary expansion is 0.000110011001100110011001100110011... so here is no hope to pack infinitely 0011 sequences in a single double. The problem with us is due to the fact that we insist computing fractional values in a different base than the one we use for either entering the values or displaying results, yet we expect results to come up "clean" in a vague sense. Anyone advising doing that seriously in obviously in a state if sin. But that's exactly what we're doing, go figure.

Besides integers, we can write correctly assignments like these:

$a = 1/2

$a = 1/4

$a = 1/8

$a = 3/8

Idealy we should use a different notation for:

$a ≆ 1/10 (assign a value that is "approximately but not exactly equal to" 1/10)

Now look at the ugly and slow code you came up with [i don't mean _your_ code is ugly, just that the _required_ code is] for a "very simple" operation and you see why I'm tempted to give that decimal lib a try in AutoIt. I believe it's impossible for a fine-tuned lib like this to be slower than that code.

[RickyD]

I did actually come up with a solution (wrote a function that adds .001 to a value if it has the trailing .005, and then round after that), but I was still curious as to why this is happening, and if it was just happening to me because I picked on that red headed girl in the 3rd grade.

Anyways, thanks for the input, everyone.

TheMario.

(Edit: Removed stupid suggestion which wouldn't work. Sorry for wasting space.)

Rick

Edited February 27, 2010 by RickyD