Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Barcelona, Spain
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts

    (9,8 - 9,1) - 0,7 = 0,000000000000001110223024625160 ??

    Dear All,

    Writing from Europe, therefor I use decimal commas instead of decimal points but that should not be the problem. W7x64, Excel 2003.

    I was checking some scanned numbers to see if they were correct. Then I did some auto-formatting (if scanned value is not equal to what excel calculates, go red) and sure enough I found some minor errors in the data.

    But what puzzled me most was that 9,8 - 9,1 did not match 0,7.

    A1=9,8 B1=9,1 C1=0,7 D1=(A1-B1) E1=(D1-C1) Now E1 shows: 1,11022E-15 which equals 0,000000000000001110223024625160

    Expanding the decimal positions of D1 it will go all the way to 0,70000000000000100000000000000000000

    This puzzles me even more, because I think that 0,70000000000000100000000000000000000 - 0,7 does not match 1,11022E-15

    I did repeat this starting from scratch typing just the above fields. :-(

    Can somebody enlighten me please?

    /\
    \7erard

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,313
    Thanks
    3
    Thanked 211 Times in 194 Posts
    Hi,
    It's a floating point precision problem. Have a read of Chip's page here: http://www.cpearson.com/Excel/rounding.htm

    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    Sr.Gerard (2012-06-13)

  4. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Barcelona, Spain
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi rory,

    A floating point precision problem is what you call it? I never suspected that 9,8 - 9,1 could provoke rounding errors. ;-)

    Thanks.

    /\
    \7erard
    Last edited by Sr.Gerard; 2012-06-13 at 06:44.

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,313
    Thanks
    3
    Thanked 211 Times in 194 Posts
    That's the joy of trying to represent fractions in binary.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #5
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,496
    Thanks
    34
    Thanked 203 Times in 182 Posts
    Take any cheap calculator
    Type in 1 7 x 7 =
    Did you expect to get 1.000000?
    You probably got 0.9999997 (the number of 9s may vary).
    Same problem of "representation of fractions as a less-than-infinite number of decimals" as above.

    Writing from Europe, therefore I use decimal commas instead of decimal points
    Not in the UK, we don't!
    BATcher

    "The trouble with quotes on the internet is that you can never know if they are genuine."
    Abraham Lincoln
    

Tags for this Thread

Posting Permissions

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