Sum function not working properly

[JLV_96]

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 income statement spreadsheet that I use to track the income and expenses of a rental I have. For each month, I have function 1 (monthly income) that adds all the income values and function 2 (monthly expenses) to add all the expense values. Then I have function 3 (monthly net income) that takes function 1 and subtracts function 2. I then have function 4 (annual net income) to sum up all the function 3. 

All my values are currency so none of them go past 2 decimal points so adding them all together should only result in a 2 decimal value; However, my total net income for the year comes out to some long decimal (ex. $500.0000001). This has happen to me a few times now with other spreadsheets that contain lots of SUM function that build on each other.

I know I can use the ROUND function to eliminate this but the problem isn't a rounding issue ($50.25 + $49.75 should equal $100.00 not $100.000000001). Plus I don't want have to add the ROUND function every time I use the SUM function just in case this error occurs. My guess is that there is some kind of bug that happens when referencing so many functions to other functions. Anyone else have this issue, know what is causing it and/or how to fix it??

[#2]

@JLV_96 

What are the sources for all your figures? Do some come from outside sources? Do some come from other mathematical operations, notably multiplication or division, where they might appear as two decimals but in fact carry a de minimus fraction?

Can you not just display two decimals (no need to round)? I mean, if it really is at the level of something like one-millionth, it's not going to become a problem anyway, is it? [I say that, fully realizing that if I were in your place, I'd want to get to the bottom of it too.]

[#3 · Accepted solution]

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.

[#4]

@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

[#5]

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))

[#6]

@ClaudioScacchi 

One more option which could help is this setting

MSFT collected all in one place here Floating-point arithmetic may give inaccurate results in Excel 

[#7]

@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

[#8]

ok

[#9]

@Sergei Baklan 

That will work if the formula was referencing a cell containing the sum, but not when the sum is part of the formula itself.

=IF(SUM(x) = SUM(y), true, false) will still fail.

With that option selected: