Home 
Search 
Today's Posts 
#1




IF formula returns error; abbreviating the formula
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  MZ 
#2




IF formula returns error; abbreviating the formula
=IF(OR(V40="No",V41="No",V42="No"),"Fail","Pass")
OR {=IF(OR(V40:V42="No"),"Fail","Pass")} *** The second formula is an ArrayFormula 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 "MZ" wrote: 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  MZ 
#3




IF formula returns error; abbreviating the formula
Try this...
=IF(COUNTIF(V40:V42,"No"),"Fail",IF(COUNTIF(V40:V4 2,"Yes")=3,"Pass",""))  Biff Microsoft Excel MVP "MZ" wrote in message ... 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  MZ 
#4




IF formula returns error; abbreviating the formula
On Thu, 7 Jan 2010 16:38:41 0500, "T. Valko"
wrote: Try this... =IF(COUNTIF(V40:V42,"No"),"Fail",IF(COUNTIF(V40:V 42,"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 / Larske 
#5




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 "Larske 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 / Larske 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
identical formula returns #VALUE! error  Excel Discussion (Misc queries)  
Array Formula returns error but only for specific columns  Excel Discussion (Misc queries)  
Sumproduct returns value error when ref is formula  Excel Discussion (Misc queries)  
Formula to sum every 4th cell returns #DIV/0! error in some column  Excel Worksheet Functions  
formula returns error in version 2003 only  Excel Worksheet Functions 