Forum Discussion
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 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??
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.htmEdit: but I'm assuming you've already checked the inputs and any precedent calculations as already suggested and confirmed that's not the problem.
8 Replies
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.htmEdit: but I'm assuming you've already checked the inputs and any precedent calculations as already suggested and confirmed that's not the problem.
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
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
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.]
