Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel calculation is 0, but displays 1.77636E-15

    Hi all,

    I've got a calculation which returns 0, but in some of the cells instead of displaying zero it displays 1.77636E-15.

    I know this is some kind of weird excel floating point issue, but what can I do to display 0?

    Thanks

    Moz

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Format the cell to display as a number with 0 decimal places.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Moz,

    I find it's a good idea to almost always use the Round function and specify the number of decimal places I want. Early in my dual track career when I was doing a lot of accounting invariably someone would point out that the column didn't add up because the total was .01 greater than the displayed numbers, darn accountants! Rounding all the calculations to 2 decimal places { =Round(A1/B7,2) } solved that problem and became my standard practice. YMMV.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,413
    Thanks
    33
    Thanked 195 Times in 175 Posts
    What you are observing is one of the limitations of floating point calculations done in binary on a computer; it's not specific to Excel.

    You can also observe a similar effect on some calculators: do 1 7 x 7 = You expect the answer 1, but on cheap calculators you'll get a result like 0.9999997
    BATcher

    Time prevents everything happening all at once...

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your help, I'll try the rounding method.

    Moz

Posting Permissions

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