Forum Discussion
Sum function not working properly
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
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:
