17 thoughts on “WTF”

1. How about precision? What if you formatted display with max decimal places we have? Or what if you truncated value to whatever significant figures you want and then compare the values?

2. Dick –

   ?dParentSubtotal – dTotalRepLineSales

   …mrt

3. It’s the old “floating-point math is not exact” problem see http://www.cygnus-software.com/papers/comparingfloats/comparingfloats.htm

   You’ll get away with comparing doubles most of the time but you’ll eventually hit the problem, even a change of compiler (as might happen when upgrading Excel to another version)can cause what were once working equivalents to fail.

   The only safe way is to either use Currency data type or to always perform a conversion to Longs (* by 100 or 1000 etc. depending on decimal precision required) – Currency is in effect a 64bit integer * 10000.

   Tom

4. I’m guessing Tom’s most likely to be right here.

   What happens if you do this:

   Round(dparentsubtotal, 2) = Round(dtotalreplinesales,2)

   ?

5. Another option is dim your variable as variants and then use cdec to convert to the decimal data type.

6. ?dparentsubtotal – dtotalreplinesales
   -7.27595761418343E-12

   You guys are really smart. Thanks.

7. I’ve run into this problem before trying to balance a spreadsheet for invoicing and balancing against the remittance advice from the customer. Rounding to the significant digits solved the problem.

   I found the “same” numbers were off in the 5th or 6th decimal place.

8. Dick –

   Easy fix…change them to singles.
   Change ’em back if you have to.

   …mrt

9. I wouldn’t coerce them into integers… That is like saying you only care if the values are within +/- 1 of each other. (Which may be what you want, but suppose you really care whether they are within a penny of each other. Dick, I’m assuming that your values are cash, since you’re an accountant.)

   Instead, I would set a tolerance, and check whether the values are within that interval. E.g.:

   Const tol = 0.0001

   If Abs(var1 – var2) < tol Then
   ‘The variables are approximately equal…
   ‘Do stuff
   Else
   ‘They’re not equal…
   ‘Do different stuff
   End if

10. Hi Matt –

    I wrote singles, you read integers. CSng(var) might still not work though.

    …mrt

11. I’d go with Matt H. Computers work in binary not decimal so this problem raises its ugly head every now and again when we force them to represent numbers as decimals. Set an acceptable tolerance and move on…

12. I think you are putting our collective leg. But, just in case you are not, this is a long standing problem with binary computers (just as the decimal system has a problem with certain fractions like 1/3).

    When working with real numbers (represented in VBA as Single or Double) check if the absolute difference between the 2 numbers is less than some acceptable threshold. I often modularize this with a a RealEqual function

    Option Explicit

    Function RealEqual(A As Double, B As Double, _
            Optional Threshold As Double = 0.00000001) As Boolean
        RealEqual = Abs(A – B) < = Threshold
        End Function

    — Dick Kusleika says:
    July 02, 2010 at 9:50 am (Edit)

    ?dparentsubtotal – dtotalreplinesales
    -7.27595761418343E-12

    You guys are really smart. Thanks.

13. @Tushar: But the same behavior can happen with any other number, 0.5 for example. Here the numbers differ by 2^-37 but ?37089.6-2^-37 still returns 37089.6 (rounded to 15sf). The problem is that 15 digits isn’t enough to uniquely identify IEEE 754 doubles (kb/78113). In fact, 37089.6 can refer to any of 13 floating point numbers (centered around 0×40E21C3333333333).

    There also seems to be a common misconception that computers can’t work precisely with decimal numbers since they mostly can’t be exactly represented in standard binary formats. Exact decimal floating point operations do exist and appear in the new 2008 IEEE standard. Excel can handle some simple decimal calculations, whereas VBA always seems to use the internal FPU function values eg ?10*0.1-1 equals 2^-54 in VBA but the equivalent in Excel equals zero.

14. Try this in excel cell

    =1-0.4-0.4-0.2 (this equals 0)
    now give =(1-0.4-0.4-0.2)=0 (this gives false)

15. Kedar: Try both formulas with and without parentheses (…), this can change decimal rounding. Agree it’s unintuitive, you can follow others’ suggestions above for workarounds.

16. Precision always is a problem for those of us coding and formulating for CAD products.

    Often our problem stems from Textbox value to math value conversion. I always create a class to contain all the math formulas and doubles. I only ever round the formula results to display on the textbox. Changes to the textbox values are saved to the class as the full double entered for math purposes.

    I NEVER use a textbox value for math formulas. They are only entry and display fields.

17. Interesting!

    wouldn’t casting both of them string, only for comparison purpose, resolve the issue?

    Visual Basic

    CStr(dparentsubtotal) – CStr(dtotalreplinesales)

    1	CStr(dparentsubtotal) – CStr(dtotalreplinesales)

    But i liked Matt H and Tushar Mehta’s solution i.e. having a treshold defined for decimal comparison.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.