May 26 2020 12:59 PM
May 26 2020 12:59 PM
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??
May 26 2020 02:48 PM
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.]
May 26 2020 06:28 PM - edited May 26 2020 06:31 PMSolution
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...).
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.
May 27 2020 04:03 AM
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
in the SUM page of the online manual they don't say NOT to use in such a case
May 27 2020 06:50 AM
May 27 2020 09:47 AM
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
May 27 2020 09:56 AM - edited May 27 2020 10:04 AM
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 ...