IF & "and"

Posted by Mo on February 03, 2001 5:05 AM

I have 5 colums in my spreadsheet. If all these 5 colums are filled in one, then my formulas display one meaning complete. e.g
=IF(AND(A1=1,B1=1,C1=1,D1=1,E1=1),1,0)
What I want is to introduce another variable, Non Applicable. So if some columns have 1 and the rest N/A (Non Applicable) then it displays one as well. Basically I want the formulas to interpret N/A as a 1 as well. So if all columns are N/A a one is displayed as well.
Any ideas?

Posted by Aladin Akyurek on February 03, 2001 7:32 AM

A quick fix is:

=OR(A1=1, A1="N/A") * OR(B1=1, B1="N/A") * OR(C1=1, C1="N/A") * OR(D1=1, D1="N/A") * OR(E1=1, E1="N/A")

You must array-enter the above formula (that is, hit control+shift+enter at the same time after typing it).

Aladin

Posted by Mark W. on February 03, 2001 10:03 AM

Mo, here's another solution:

{=(COUNT(MATCH(A1:E1,{1,"N/A"},0))=5)+0}

This too is an array formula which must be entered
using Shift+Ctrl+Enter. The braces, {}, are not
entered by you, but rather supplied by Excel to
indicate that this is an array formula.

Posted by Mark W. on February 03, 2001 10:07 AM

Re: IF &

Aladin, you formula, =OR(A1=1, A1="N/A") * OR(B1=1, B1="N/A") * OR(C1=1, C1="N/A") * OR(D1=1, D1="N/A") * OR(E1=1, E1="N/A"),
doesn't need to be entered as an array formula. None of its arguments nor its result are arrays.

Posted by Mark W. on February 03, 2001 10:09 AM

Clarification

Mo, I should say that the outmost braces, {}, are
not entered by you, but rather supplied by Excel to
indicate that this is an array formula.

Posted by Aladin Akyurek on February 03, 2001 10:28 AM

Re: IF &

Yep. One's most active routines get triggered once in a while even if not needed, I guess.

Mark, did you read that response at Element K: "Try tea leaves or chicken entrails." Best solution ever proposed. Someone had "une crise d'intelligence" there.

Aladin

Posted by Mark W. on February 03, 2001 10:36 AM

Re: IF &

Hmmm, I wonder who could have said such a thing... ;)

Posted by Mo on February 03, 2001 12:12 PM

Thanks Alladin & Mark

Thankyou both of you for your very clever solutions.