Forum Discussion
oddest issue I have ever come across
So working on some financial figures.
A1 28,179,010.06
B1 14,000,000
C1 12,179,010.06
D1 2,000,000
E1 contains the formula a1-b1-c1-d1. I get zero. However, the cells are formatted for accounting. So I am supposed to get a hash "-" when value is 0. Instead I saw (0.00). Which I thought was odd. I changed the decimal places to 20, and I saw this - .0000000186264514923 (I had to manually type that so might be off by a zero).
So I did this on my work computer, on my personal laptop, called someone else and they got the same thing. However, when I add about b1,c1, and d1, then subtract from a1 I get a zero with no funny business. I have hundreds of calculations in my workbook and so far everything is zeroing out, from what I have seen the way it is supposed to.
So what in the heck is going on? Thank you.
Since we use decimal numbers and Excel internally uses binary numbers with a finite precision, tiny rounding errors may occur.
Since all your numbers have at most 2 decimal places, you should round the result of the formula to 2 decimal places too:
=ROUND(A1-B1-C1-D1,2)
2 Replies
Since we use decimal numbers and Excel internally uses binary numbers with a finite precision, tiny rounding errors may occur.
Since all your numbers have at most 2 decimal places, you should round the result of the formula to 2 decimal places too:
=ROUND(A1-B1-C1-D1,2)
- Thank you. I just changed the formatting to numbers and how negatives will display so that everything looks uniform. Something so minor was driving me nuts. I appreciate you explaining what is going on.
