IF formula returns error; abbreviating the formula
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.
--
Biff
Microsoft Excel MVP
"Lars-Åke Aspelin" wrote in message
...
On Thu, 7 Jan 2010 16:38:41 -0500, "T. Valko"
wrote:
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
|