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,280
    Thanks
    3
    Thanked 191 Times in 177 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 07:44.

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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,414
    Thanks
    33
    Thanked 195 Times in 175 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

    Time prevents everything happening all at once...

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
  •