Calculation Issue

[NSAMSA]

I'm sure this isn't a new issue, but my calculations are doing some strange things. I have a query in which the following calculation is done:

([eQuantity]*[tQuantity])/minUnits

As an example, the following are the associated numbers

eQuantity - 9.519
tQuantity - 1
minUnits - 1

This should come out to 9.519. However, the number being returned is 9.51900005340576

I do not know where the extra digits are coming from. 9.519 was manually typed in, so this isn't the result of another calculation, or a longer number being input elsewhere. This is the case for multiple rows. Please let me know what I am missing.

Thank you

 

[sneuberg]

I was able to replicate what you are seeing when I made the data types Single. I believe what you are seeing is the round off error introduced by the conversion of decimal to binary. Singles provide about 7 digits of precision. If you want or need greater precision you could change the type to double. If you just don't want to see these digits then use Format but at the place where they would be viewed, e.g., the report.


PS: You can demonstrate the effect of binary round off by making eQuantity equal to 9.1875 which is 9 + 3/16 which can be represented exactly in binary. The result of the calculate is 9.1875. If you change eQuantity to 9.1876 the result is 9.18760013580322

 

[The_Doc_Man]

This is a classic case of conversion rounding.

The value 9.51900005340576 as a SINGLE involves 24 bits (1 hidden, 23 explicit) of mantissa, or fraction, and of course the sign and exponent. If you multiply this number by 2^24 (=16777216) then you get the number binary-shifted 24 bits, and the result is 159702319.99999997116416 - and that tells you that your actual number is within 0.00000002883 (and a few more digits) of being exact. How does this inexact computation happen?

The answer is that when working with binary numbers, the fraction 1/10 is a repeating decimal series for which the bits don't come out even. If I recall correct, 1/10th expressed as a binary fraction is 0.00011001100110011...00110011...( to infinity). Seems unfair, doesn't it? But it's those repeating infinite digits that cause the rounding when you are converting fractions in the range 1>x>0 - because of course subtracting an infinite binary sequence from ANY binary sequence will yield another infinite binary sequence (the exception being if one of the sequences is an integer multiple of the other.)

Next question: What do you do to stop this?

Option 1: Switch to DOUBLE and move the problem another 32 bits to the right.

Option 2: Realize that you CANNOT get rid of the rounding - but you don't have to show it. So use an explicit FORMAT( number, "####.####" ) for display purposes and realize that the display is merely HIDING the bits resulting from this computation.

 

[pbaldy]

Option 3: Switch to Currency (or maybe Number/Decimal), which is not a floating point data type like Single and Double are.

 

[The_Doc_Man]

Right again, Paul. Scaled integers work correctly, too. I just don't know how general his units are. If they are NEVER more than 3 digits precision, no sweat. If this is more variable, then we might need to know more.

The important thing for the OP (or anyone else) to remember is that decimal fractions on a binary machine ALWAYS have rounding issues as long as you are in any of the floating point formats, whether we are talking SINGLE, DOUBLE, IEEE T format, or IEEE X format. ALL of them have the same issue. Some just hide it better than others.

 

[pbaldy]

By the way Doc, liked your explanation. I've long known of the issues with floating point numbers, but not why. Thanks!