ISNA Function

[Walley]

Hi
I use IF(ISNA(LOOKUP(1100;C6:C25));"";(LOOKUP(1E+100;C6:C25)-C6)*1200)
function and I apply Conditional formating with Icon set to fill the cell
with green color if the value > 0, red if < 0 and yellow if 0, it work, but
when the cell is empty it fill with green color. If there is any way to leave
the color of the cell unchanged
if the cell empty.
Thank

 

[T. Valko]

You'd have to include a test to make sure the cell does not contain the
formula blank.

Instead of the condition being >0 it should be:
=AND(cell_ref>0;cell_ref<>"")

 

[Harlan Grove]

I use IF(ISNA(LOOKUP(1100;C6:C25));"";(LOOKUP(1E+100;C6:C25)-C6)*1200)
function and I apply Conditional formating with Icon set to fill the cell
with green color if the value > 0, red if < 0 and yellow if 0, it work, but
when the cell is empty it fill with green color. If there is any way to leave
the color of the cell unchanged if the cell empty.

To Excel, when transition formula is disabled (usual state unless the
workbook in question was originally created with Lotus 123) all test
strings, including "", are greater than all numbers. When transition
formula evaluation is enabled, all text strings are treated as equal
to zero.

You need to change your conditional formatting formula (or change your
conditional formatting criterion to a formula) for the > 0 test to

=N(X99)>0

 

[Walley]

Thank you for help. Do you mean that I should include
AND(cell_ref>0;cell_ref<>"") in
IF(ISNA(LOOKUP(1100;C6:C25));"";(LOOKUP(1E+100;C6:C25)-C6)*1200) formula, and
how. thanks

 

[T. Valko]

No, you would use that as one of the conditions for conditional formatting.

fill the cell with green color if the value > 0

Instead of the condition being >0 use:

=AND(cell_ref>0;cell_ref<>"")

Or, use Harlan's suggestion. It does the same thing and is more compact.