Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 7th 10, 09:09 PM posted to microsoft.public.excel.misc
mz mz is offline
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2007
Posts: 40
Default 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   Report Post  
Old January 7th 10, 09:18 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 561
Default 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

  #3   Report Post  
Old January 7th 10, 09:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old January 7th 10, 09:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 913
Default 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 / Lars-ke
  #5   Report Post  
Old January 7th 10, 11:02 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
identical formula returns #VALUE! error Tasso Excel Discussion (Misc queries) 3 June 1st 09 04:57 PM
Array Formula returns error but only for specific columns pblenis Excel Discussion (Misc queries) 3 March 9th 07 11:02 PM
Sumproduct returns value error when ref is formula wx4usa Excel Discussion (Misc queries) 3 January 21st 07 04:57 PM
Formula to sum every 4th cell returns #DIV/0! error in some column Brent Excel Worksheet Functions 5 March 16th 06 07:20 PM
formula returns error in version 2003 only Wayne Cameron Excel Worksheet Functions 0 October 31st 05 05:00 PM


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017