Conditional Formatting

[SONJOE JOSEPH]

Dear Excel Experts,

My conditional format not coming correct. The rules are as follows:-

1. In columns H from H3 to H9 if the values are > 20000 then the text color should be dark green.
2. if values < 20000 then the text color should be dark red
3. if values = 20000 then the text color should be yellow.

Now u can see in the working file from H6 to H9 its showing green color that should not come unless the transaction is fully entered.

Working file is attached ...do help me out.

Regards,
Sonjoe

 

[pecoflyer]

This is caused by the use of the empty text string "" in your formula =IF(ISBLANK(A6),"",H5+G6) when the A cell is empty
Before comparing values, XL first evaluates and compares the TYPE() of the values to be compared. (don't ask me why...)( other programs like OpenOffice do not do this)
For text it is 2, and for numbers 1. So XL first says "is TYPE("") larger than type(20000)?".
Answer is TRUE so CF is applied...
Perhaps change the condition for CF to =AND(H3>20000,H3<>"")

 

[SONJOE JOSEPH]

This is caused by the use of the empty text string "" in your formula =IF(ISBLANK(A6),"",H5+G6) when the A cell is empty
Before comparing values, XL first evaluates and compares the TYPE() of the values to be compared. (don't ask me why...)( other programs like OpenOffice do not do this)
For text it is 2, and for numbers 1. So XL first says "is TYPE("") larger than type(20000)?".
Answer is TRUE so CF is applied...
Perhaps change the condition for CF to =AND(H3>20000,H3<>"")

Thank you friend..its working perfectly fine.

Regards,

Sonjoe

 

[pecoflyer]

Most welcome