if function and ifblank() function

[Bob Phillips]

=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]

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.

 

[Dave F]

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

 

[BJ]

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.