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

 

[aaa89]

Must be very difficult... Cause when using just - it does equal 0, hard to understand why the 'logic' of IF should effect that... So I guess my best and only option is to use the ROUND? Never used that one either before, whats teh ',2' for?

 

[T. Valko]

Must be very difficult... Cause when using just - it does equal 0, hard to understand why the 'logic' of IF should effect that... So I guess my best and only option is to use the ROUND? Never used that one either before, whats teh ',2' for?

Yes, use rounding.

The 2 means to round to 2 decimal places (since you're apparently dealing with currency).

 

[aaa89]

Thanks man! You nailed it!

Since I know you know your stuff, a related question in the custom formatting I know how to apply standard colors, [red] [green] and one of the 56 pallet colors [color #], is there a way to apply a custom RGB color?

 

[T. Valko]

Thanks man! You nailed it!

Since I know you know your stuff, a related question in the custom formatting I know how to apply standard colors, [red] [green] and one of the 56 pallet colors [color #], is there a way to apply a custom RGB color?

What version of Excel are you using?

 

[aaa89]

2010

 

[T. Valko]

2010

I don't have Excel 2010 but I do have Excel 2007 and it should be pretty much the same in both versions.

Select the cell to be formatted
Right click>Format Cells
Select the Fill tab
Click More Colors
Select the Custom tab

 

[aaa89]

I was referring to do it from within the custom format window, as I posted using the color name or number from the pallet.

 

[T. Valko]

I was referring to do it from within the custom format window, as I posted using the color name or number from the pallet.

Hmmm...

I don't know if it can be done there. If it can I don't know how!

 

[aaa89]

Thanks!

 

[T. Valko]

Thanks!

You're welcome. Good luck!