oddest issue I have ever come across

[Ivan1280]

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.

[#2 · Accepted solution]

@Ivan1280 

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)

[#3]

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.