Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Excel 2003: subtraction error ?

    This is a bit of a surprise !

    I have attached a fragment of a very large Workbook which contains a simple subtraction. I had expected the number in D2 to be zero but it is not.

    The numbers in B2, E2, F2, G2 and H2 are all typed, not calculated.

    The outcome is that a test for zero is failing - I know I can workaround that in this simple instance but it will be very difficult to allow for similar errors everywhere in the Workbook, if they exist.

    Anyone have an explanation ?
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Try this formula: =B2-SUM(E2:H2)
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    That is even more intriguing.

    The root cause seems to be described here: http://support.microsoft.com/kb/78113
    but now I am bemused why SUM gives a different answer from subtraction.

    A little further fiddling around reveals that adding negative numbers (which is what SUM does of course) gives accurate answers (in cases I have tried) whereas subtracting positive numbers does not. Now there's a subtle difference, not one covered by the linked article.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    For all intents and purposes for me, -0.000000000000909494701772928 is close enough to zero, but excel is correct it is not exactly zero. I would round (to an appropriate tolerance) before comparing to zero. Floating point issues like this always can arise. Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •