+ Reply to Thread
Results 1 to 3 of 3

Wrong Sum() Showing

  1. #1
    Registered User
    Join Date
    07-26-2007
    Posts
    4

    Wrong Sum() Showing

    I have a formula (see below) in a spreadsheet that should return the sum. Instead it returns an error because instead of the sum equaling zero, it equals 0.000000000014551915228366900000. The numbers that it is summing are typed and only have 2 decimal places. Rounding should not be an issue.

    Does anyone have any idea why it is not returning zero?

    Formula:
    =IF(SUM(A1:A6)=C1,SUM(A1:A6),"error")
    (I also attached the spreadsheet for review)

    Thank you for your help!
    Attached Files Attached Files

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Its because numbers are stored in Binary format

    http://support.microsoft.com/kb/214118

    http://www.cpearson.com/excel/rounding.htm
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Contributor
    Join Date
    07-05-2007
    Location
    Lexington, MA
    Posts
    302
    Try the following version of your formula:

    =IF(round(SUM(A1:A6),2)=round(C1,2),round(SUM(A1:A6),2),"error")

    The round function will throw away small bits of number that are accumulated when manipulating numbers in Excel.

    For example, the number 2.33 cannot be stored exactly. It might have an actual machine value of 2.33000000000016 . The rounding throws away the insignificant differences.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1