ISNA Function

  • Thread starter Thread starter Walley
  • Start date Start date
W

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
 
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<>"")
 
Walley said:
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.
Click to expand...

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
 
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
 
No, you would use that as one of the conditions for conditional formatting.
fill the cell with green color if the value > 0
Click to expand...

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.
 
Back
Top