Zero formula is showing red (sometimes)

[irvineboy]

This is driving me crazy as I cannot figure out why my formulas that equal 0, are showing as a RED color.

I am adding up two cells. One cell is $102.60 (A1). One cell is $84.30 (A2). The total cell is $102.60+$84.30 = $186.90 (A3) The cell formats are all represented as "currency".

I have another cell A9 that points to cell A3 and another cell B9. The equation is A9 minus B9 plus C9 equals D9. In the B9 cell, I manually type in $186.90. So $186.90 minus $186.90 should equal 0. The 0 should be black color since only negative numbers are shown in parenthesis and colored red. But for some reason, the zero is in parenthesis and colored red. It makes no sense. $186.90 minus $186.90 = 0

But if I expand the cell D9 by expanding decimals, it shows $0.000000000000000002842 which is strange. Why is there a very small decimal number?

Now if I manually type in $186.90 in cell A12, instead of pointing it to cell A3, then the 0 is black in cell D12. What is going on?





Please see attached.

 

[irvineboy]

Now if I change the number in cell A2 from $84.30 to $84.00 and then manually type $186.60 in cell B7, then the equation in cell D7, will show a black 0.
This is so random. Certain numbers will represent a red 0 while certain numbers will represent a black 0. Help!!!

 

[Peter_SSs]

Welcome to the MrExcel board!

It is to do with how Excel does its calculations. See here for more info.
Add a ROUND to your current formula & that should resolve it.
eg

=ROUND(A9-B9,2)

 

[irvineboy]

Welcome to the MrExcel board!

It is to do with how Excel does its calculations. See here for more info.
Add a ROUND to your current formula & that should resolve it.
eg

=ROUND(A9-B9,2)

But there is no rounding. $186.90 is $186 plus 90 cents. It is not like a third that is represented as a 0.3333333333333333 (with infinite number of 3's). It is a simple 186.90 not 186.89745683437 I am still confused

 

[irvineboy]

Why is it in the second case when I change one of the figures from $84.30 to $84, the total now becomes $186.60 instead of $186.90 like in the first example, there is no rounding issue?

 

[Peter_SSs]

As I said before: It is to do with how Excel does its calculations (although I should have added "and stores its numbers")
Did you read the linked article?
Did you try adding the ROUND to your current formula?

 

[Peter_SSs]

Also, have a look at these examples where, if you look at the formulas a human would calculate all three results as zero, but with Excel that is not the case.

 

[irvineboy]

As I said before: It is to do with how Excel does its calculations (although I should have added "and stores its numbers")
Did you read the linked article?
Did you try adding the ROUND to your current formula?

I did add ROUND to formula and that solves it.
I did read the article but still did not understand it. I am not familiar with a binary system at all nor do I understand the floating decimal concept. I re-read it a few times but doesn't make sense. 60 cents is 0.60 not 0.5989. 90 cents is 0.90 not 0.8987 Unless the computer reads it that way.

 

[Peter_SSs]

It is not an easy thing to understand and I certainly do not profess to fully understand it. Also, it is not that Excel stores 0.6 as 0.5989, it depends on the circumstances and order of dealing with numbers as can be see from the examples in the post 7 link above. There, all three calculations use exactly the same starting numbers but the calculations are in a different order and the results are 3 very slightly different values.

I did add ROUND to formula and that solves it.

That is the main thing!