- **Excel Discussion (Misc queries)**
(*https://www.excelbanter.com/excel-discussion-misc-queries/*)

- - **IF formula returns error; abbreviating the formula**
(*https://www.excelbanter.com/excel-discussion-misc-queries/252733-if-formula-returns-error%3B-abbreviating-formula.html*)

IF formula returns error; abbreviating the formulaI 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 |

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 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 "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 |

IF formula returns error; abbreviating the formulaTry 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 |

IF formula returns error; abbreviating the formulaOn 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 / Lars-ke |

IF formula returns error; abbreviating the formulaMight 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 |

All times are GMT +1. The time now is 04:34 PM. |

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.

ExcelBanter.com