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