Forum Discussion

JLV_96's avatar
JLV_96
Copper Contributor
May 26, 2020
Solved

Sum function not working properly

I have an issue with the SUM function in excel where I have it sum numbers with only 2 decimals but the result is a longer decimal (example: 2.00000001). Here is my specific example I have a basic ...
excel
Formulas and Functions
  • JMB17's avatar
    JMB17
    May 27, 2020

    It is a rounding problem. The computer converts your numbers to binary, performs the calculations, then converts it back to base 10. There are some base 10 decimals that cannot be represented exactly as binary fractions. So, you can get small rounding errors.

    Any time you are working with decimals, you should round your calculations even if all of the inputs are only to a certain decimal place (especially if you are using something like IF(calcresult=x...).

    http://www.cpearson.com/Excel/rounding.htm

     

    Edit: but I'm assuming you've already checked the inputs and any precedent calculations as already suggested and confirmed that's not the problem.

ClaudioScacchi's avatar
ClaudioScacchi
Copper Contributor
May 27, 2020

JMB17 

 

Hi

I have the same problem using a simple function like this:

=IF(SUM(B16:G16)=SUM(H12:H15);"ok";"error: " & SUM(B16:G16)-SUM(H12:H15))

 

sum are identical but excel display something like that

 

error: -8,5265128291212E-14

 

in the SUM page of the online manual they don't say NOT to use in such a case

 

 

 

JMB17's avatar
JMB17
Bronze Contributor
May 27, 2020
You can use it that way, but you have to round the results to the desired number of decimals if B16:G16 and/or H12:H15 contains decimal data. If the data is integers, then you don't need to round as all integers can be expressed exactly in binary.

=IF(ROUND(SUM(B16:G16),2)=ROUND(SUM(H12:H15),2);"ok";"error: " & ROUND(SUM(B16:G16),2)-ROUND(SUM(H12:H15),2))
  • ClaudioScacchi's avatar
    ClaudioScacchi
    Copper Contributor
    May 27, 2020

    JMB17 

    I'm comparing values in Euro ... a common task, I think

    so everytime I must remember to round if I want use the result in a IF function ...

    using excel from the 2.1 version and I never had to use a IF(SUM(...) with currency ...

     

     

    tnks

     

Resources