IF formula returns error; abbreviating the formula

[MZ]

I used the following formula to evaluate the "Yes/No" responses to several
questions.
If any of the responses are "No" then the formula should return "Fail"; only
if all are "Yes", then it should return "Pass"

=IF(OR(V40,V41:V42)="No","Fail","Pass")

V40, V41, and V42 are the cells that contain the Yes/No responses

The formula returned an error.
Any suggestions for correction?
In addition, if there are many questions, how would I abbreviate the query
of cells so that I do not manually list them, as I did above - i.e. instead
of V40,V41,V42, use something like a colon between the first and last cell -
V40:V42?

Thank you

 

[מיכ×ל (מיקי) ×בידן]

=IF(OR(V40="No",V41="No",V42="No"),"Fail","Pass")
OR
{=IF(OR(V40:V42="No"),"Fail","Pass")}
*** The second formula is an Array-Formula so press CTRL+SHIFT+ENTER, at the
same time, rather then just ENTER.
DO NOT type the curly braces { }. Excel will display them automatically !
Micky

 

[T. Valko]

Try this...

=IF(COUNTIF(V40:V42,"No"),"Fail",IF(COUNTIF(V40:V42,"Yes")=3,"Pass",""))

 

[Lars-Åke Aspelin]

Try this...

=IF(COUNTIF(V40:V42,"No"),"Fail",IF(COUNTIF(V40:V42,"Yes")=3,"Pass",""))

And you may replace the 3 with ROWS(V40:V42) in order to prepare for
the challenge of "many questions".
Then you just replace V40:V42 with V40:V100 or whatever range you
have.
The safest way would to create a named range, myQuestions, and have
that in all three places of the formula.

Hope this helps / Lars-Åke

 

[T. Valko]

Might be easier to use an array formula**:

=IF(OR(V40:V42="No"),"Fail",IF(AND(V40:V42="Yes"),"Pass","")

=IF(OR(V40:V100="No"),"Fail",IF(AND(V40:V100="Yes"),"Pass","")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.