Thread: ISNA Function
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default ISNA Function

Walley wrote...
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