Formula and formatting question

[aaa89]

Hi all, I need some help with a custom format. I'm trying to get green for positive, "Red $ -0.00" for negative", and black for zeros.

[Color10]_($* #,##0.00_);[Red]_($* -#,##0.00_);_($* #,##0.00,""_)

The cell formula is AB9 =IF(V9="","",V9-AA9). I used the IF to avoid the #value!, cause V9 =IF(AND(L9:P9>0,K9>""),U9*O9,""), and AA9 is a number I manually enter.

My problem is - When AB9 equals ZERO it shows me "$0.00" but in green, not black.
I suspected that might be the cell is displaying 0 but equals more, so in a blank cell, leaving the format to general I typed =AB9, instead of getting a 0 I got 3.55271E-14, why is that, is there anything wrong with my formula?

Thanks

 

[dannac]

 

[aaa89]

Thanks for your reply. But this doesn't work either cause the value is greater than 0 as I posted. For some reason it equals 3.55271E-14 and only shows as a zero. Secondly I have to use the custom format to get "-30.00" instead of "(30.00)" for negative values.

So I need another solution as to why the cell equals to 3.55271E-14.
Thanks

 

[Kasey_]

Best thing that I can think of is using Conditional Formatting.

 

[aaa89]

Again the conditional formatting does NOT help as I posted, cause the value equals to 3.55271E-14, its only displayed as $0.00" when i select the formatting to Accounting.

 

[T. Valko]

Again the conditional formatting does NOT help as I posted, cause the value equals to 3.55271E-14, its only displayed as $0.00" when i select the formatting to Accounting.

=IF(V9="","",V9-AA9).

You need to use rounding on the formula.

=IF(V9="","",ROUND(V9-AA9,2))

That 3.55271E-14 is what's known as a "rounding error".

Understanding IEEE floating point errors...

http://support.microsoft.com/kb/42980

How to correct rounding errors in floating point arithmetic...

http://support.microsoft.com/kb/214118

 

[aaa89]

Thanks! Finally someone pointing in the right direction. This whole concept is tooooo complicated for me at the moment.

I'm just curious as to why when AB =V9-AA9 I get the true value 0, and adding the IF =IF(V9="","",V9-AA9) to avoid getting the VALUE# when its not filled in, that's when I'm getting these rounding issues.
THanks

 

[T. Valko]

Thanks! Finally someone pointing in the right direction. This whole concept is tooooo complicated for me at the moment.

I'm just curious as to why when AB =V9-AA9 I get the true value 0, and adding the IF =IF(V9="","",V9-AA9) to avoid getting the VALUE# when its not filled in, that's when I'm getting these rounding issues.
THanks

Try this test formula EXACTLY as shown:

=(V9-AA9)=0

 

[aaa89]

Try this test formula EXACTLY as shown:

=(V9-AA9)=0

Come up as FALSE. I copied it exactly (although I got no clue what it means...)

 

[T. Valko]

Come up as FALSE. I copied it exactly (although I got no clue what it means...)

It means that when those 2 numbers are subtracted the result is not EXACTLY 0 so the formula returns FALSE. That's where you're getting the floating point math error.

It's a difficult concept to get used to!