Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISNA Function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISNA Function
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_ref0;cell_ref<"") -- Biff Microsoft Excel MVP "Walley" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISNA Function
Thank you for help. Do you mean that I should include
AND(cell_ref0;cell_ref<"") in IF(ISNA(LOOKUP(1100;C6:C25));"";(LOOKUP(1E+100;C6: C25)-C6)*1200) formula, and how. thanks "T. Valko" wrote: 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_ref0;cell_ref<"") -- Biff Microsoft Excel MVP "Walley" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISNA Function
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_ref0;cell_ref<"") Or, use Harlan's suggestion. It does the same thing and is more compact. -- Biff Microsoft Excel MVP "Walley" wrote in message ... Thank you for help. Do you mean that I should include AND(cell_ref0;cell_ref<"") in IF(ISNA(LOOKUP(1100;C6:C25));"";(LOOKUP(1E+100;C6: C25)-C6)*1200) formula, and how. thanks "T. Valko" wrote: 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_ref0;cell_ref<"") -- Biff Microsoft Excel MVP "Walley" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF?ISNA?MATCH? Function? | Excel Discussion (Misc queries) | |||
Vlookup - ISNA function | Excel Discussion (Misc queries) | |||
ISNA match function help | Excel Worksheet Functions | |||
ISNA function | Excel Discussion (Misc queries) | |||
example if IF(ISNA()) function | Excel Worksheet Functions |