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,547
    Thanks
    152
    Thanked 1,400 Times in 1,222 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,560
    Thanks
    384
    Thanked 1,480 Times in 1,346 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,451
    Thanks
    33
    Thanked 196 Times in 176 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

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

  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
  •