Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function and ifblank() function
=IF(AND(ISNUMBER(A1),B1=""),""100%"),IF(AND(A1="", B1=""),"---",IF(AND(ISNUMB
ER(A1),isNUMBER(B1)),A1-B1,""))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BJ" wrote in message ... Hi List, Here is what I am looking for: Column A Column B Column C Row 1 1000 Blank(hasF) 100% Row 2 Blank(hasF) Blank(hasF) --- Row 3 100 10 (100-10)/100% if Column A has number, Column B is blank, Column C shows 100%; if both blank shows '---'; if both have numbers, do the calculation (ColA - ColB)/ColC. i tried to use isblank() function to see whether the cell is blank, since there is formula, the isBlank function didn't work. I tried to use trim(), len() = 0, all didn't work. can some help? Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function and ifblank() function
Hi List,
Here is what I am looking for: Column A Column B Column C Row 1 1000 Blank(hasF) 100% Row 2 Blank(hasF) Blank(hasF) --- Row 3 100 10 (100-10)/100% if Column A has number, Column B is blank, Column C shows 100%; if both blank shows '---'; if both have numbers, do the calculation (ColA - ColB)/ColC. i tried to use isblank() function to see whether the cell is blank, since there is formula, the isBlank function didn't work. I tried to use trim(), len() = 0, all didn't work. can some help? Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function and ifblank() function
Try:
=IFAND((ISNUMBER(A1),ISBLANK(B1),C1=1),100%,IF(AND (ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)),"---",IF(AND(ISNUMBER(A1),ISNUMBER(B1),ISNUMBER(C1)),( A1-B1)/C1))) You may need to check the parentheses; I have a headache from entering them all in. But that's essentially the logic of your question if I understand it correctly: "IF A1 is a number AND B1 ISBLANK AND C1 equals 1, THEN 100%, ELSE IF A1, B1, and C1 are blank, THEN ---, ELSE IF A1, B1, AND C1 are numbers, THEN (A1-B1)/C1" Dave -- Brevity is the soul of wit. "BJ" wrote: Hi List, Here is what I am looking for: Column A Column B Column C Row 1 1000 Blank(hasF) 100% Row 2 Blank(hasF) Blank(hasF) --- Row 3 100 10 (100-10)/100% if Column A has number, Column B is blank, Column C shows 100%; if both blank shows '---'; if both have numbers, do the calculation (ColA - ColB)/ColC. i tried to use isblank() function to see whether the cell is blank, since there is formula, the isBlank function didn't work. I tried to use trim(), len() = 0, all didn't work. can some help? Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function and ifblank() function
Thank you for your quick reply.
I posted my question in a rush, and forgot to mention that in column A and B the cells are all referenced from another sheet. so even there is no value in it, it has formula. so isnumber and isblank all not working correctly. "Dave F" wrote: Try: =IFAND((ISNUMBER(A1),ISBLANK(B1),C1=1),100%,IF(AND (ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)),"---",IF(AND(ISNUMBER(A1),ISNUMBER(B1),ISNUMBER(C1)),( A1-B1)/C1))) You may need to check the parentheses; I have a headache from entering them all in. But that's essentially the logic of your question if I understand it correctly: "IF A1 is a number AND B1 ISBLANK AND C1 equals 1, THEN 100%, ELSE IF A1, B1, and C1 are blank, THEN ---, ELSE IF A1, B1, AND C1 are numbers, THEN (A1-B1)/C1" Dave -- Brevity is the soul of wit. "BJ" wrote: Hi List, Here is what I am looking for: Column A Column B Column C Row 1 1000 Blank(hasF) 100% Row 2 Blank(hasF) Blank(hasF) --- Row 3 100 10 (100-10)/100% if Column A has number, Column B is blank, Column C shows 100%; if both blank shows '---'; if both have numbers, do the calculation (ColA - ColB)/ColC. i tried to use isblank() function to see whether the cell is blank, since there is formula, the isBlank function didn't work. I tried to use trim(), len() = 0, all didn't work. can some help? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|