+ Reply to Thread
Results 1 to 11 of 11

Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is N

  1. #1
    Registered User
    Join Date
    09-10-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is N

    Hey,

    I have a spreadsheet that will summarise assessments. There are a number of questions (1a, 1b etc. to 3b):
    (sorry for the confusion but there is an empty row after 1e (row 7)


    Criteria Plan 1
    1a Y
    1b NA
    1c Y
    1d Y
    1e Y

    2a Y
    2b Y
    2c Y
    2d Y
    2e Y
    2f N
    2g Y
    2h N
    2i Y

    3a N
    3b Y
    Total for each plan (number of Y and NA answers) 13
    Pass or Fail PASS


    If there are 13 or more Yes or NA answers they PASS. I have done this by using the formula =(COUNTIF(B2:B19,"Y"))+(COUNTIF(B2:B19,"NA")). This gives the total for each plan (13) in B20 which is used below.

    The Pass or Fail formula is =(IF(B20<=12,"FAIL","PASS"))


    Then I need to add another condition or exception. If the assessment has N at questions 1a, 2a or 3a they automatically fail and this is where I'm getting stuck. If I could combine two formula it would be something like this:

    =(IF(B20<=12,"FAIL","PASS")) except when (B2, B8 or B18="N", "FAIL")

    In other words the assessment needs 13 or more Y or NA's to pass. If there is a N at questions 1a, 2a and 3a they automatically fail.

    Thanks very much for your help!
    Attached Files Attached Files
    Last edited by Researcher1111; 09-13-2013 at 01:07 AM. Reason: Attachment added

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    welcome to the forum. first off, you could do with less brackets:
    =COUNTIF(B2:B19,"Y")+COUNTIF(B2:B19,"NA")
    =IF(B20<=12,"FAIL","PASS")

    secondly, you can further shorten the first formula:
    =SUM(COUNTIF(B2:B19,{"Y","NA"}))

    and for your question, you can do this:
    =IF(OR(B20<=12,B2="N",B8="N",B18="N"),"FAIL","PASS")
    so when B20 <=12, or when the 3 cells is equals to "N", they fail

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    07-09-2012
    Location
    India
    MS-Off Ver
    Excel 97/2003/2007/2010
    Posts
    67

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    hi benishiryo,

    your answer is great...

    OR function returns pass if any of one condition is true.. please revert.

    regards,
    sathya

  4. #4
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    Try one of these

    Excel 2007 or higher
    =IF(OR(B20<13,SUM(COUNTIFS($A$2:$A$19,{"1a";"2a";"3a"},$B$2:$B$19,"N"))),"Fail","Pass")

    Earlier versions
    =IF(OR(B20<13,SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$19,{"1a";"2a";"3a"},0)),--($B$2:$B$19="N"))),"Fail","Pass")
    Marcelo Branco

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    Quote Originally Posted by sathiyamoorthy View Post
    h
    OR function returns pass if any of one condition is true.. please revert.
    hmmm to not confuse your question with OP's pass/fail, i shall not say it returns "pass". the OR function returns TRUE as long as one condition is met. so yes, you're right if that's what you meant. so when either of them turns TRUE, it will show as "FAIL" for OP's case

  6. #6
    Registered User
    Join Date
    09-10-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    Thank you all very much for your help!

    Further to this I have one more issue.
    As you can see all the answers are for Plan 1. An organisation could have up to 20 plans each with their own Pass or Fail mark.
    I then need to calculate how many Pass/Fails there are within the organisation and they then get a Pass if 80% of plans pass. I hope this wording isn't too confusing.

    Below I have a formula (with too many brackets I know) that calculates the org's mark based on 9 plans

    =IF(((COUNTIF(B21:J21,"PASS"))/((COUNTIF(B21:J21,"PASS"))+(COUNTIF(B21:J21,"FAIL"))))>=0.8,"PASS","FAIL")

    B21:J21 is the fail/pass marks for each plan where the formula was my original problem. It's basically trying to do: pass / (pass+fail) and then if that's =>.8 the org get's a pass.

    Now the issue/exception. If the org has 5 plans or less this 80% rule doesn't apply to them.

    Hope this makes sense,
    Thanks again.

  7. #7
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    You didn't specify what should be done when an org has 5 plans or less, so I left that case empty:

    =IF(COUNTA(B21:J21<=5,"",IF(COUNTIF(B21:J21,"Pass")/COUNTA(B21:J21)>=0.8,"Pass","Fail"))
    When I say semicolon, u say comma!

  8. #8
    Registered User
    Join Date
    09-10-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    Thanks L-Drr your formula has helped a lot im just not sure how to combine everything I need.

    I would like the one cell to determine if the organisation passes or fails based on the following:
    - Pass as a whole if they get 80% or more 'Pass' for the plans
    =IF(((COUNTIF(B21:J21,"PASS"))/((COUNTIF(B21:J21,"PASS"))+(COUNTIF(B21:J21,"FAIL"))))>=0.8,"PASS","FAIL")

    - if there are less than 5 plans they automatically fail
    =IF(COUNTIF(B21:AZ21,"PASS")<5,"FAIL","PASS")
    Although I think I need to change this so less than 5 plans says "Less than 5 plans" instead of Fail

    So the cell could have the following outcomes:
    Pass: has more than 5 plans, 80% of those plans are Pass
    Fail: has more than 5 plans, <80% of those plans are Pass
    Less than 5 plans: There are less than 5 plans

    Thank you again, you have been a great help

  9. #9
    Registered User
    Join Date
    09-10-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    Bump no response

  10. #10
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    Quote Originally Posted by L-Drr View Post
    =IF(COUNTA(B21:J21<=5,"",IF(COUNTIF(B21:J21,"Pass")/COUNTA(B21:J21)>=0.8,"Pass","Fail"))
    You can put the text "There are less than 5 plans" between the red quotations

  11. #11
    Registered User
    Join Date
    09-10-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

    Thanks very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need a formula to Pass/Fail data entered based on several variables....
    By smurf0617 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-21-2013, 01:10 PM
  2. Help with Pass / Fail Formula
    By milovisk in forum Excel General
    Replies: 4
    Last Post: 03-20-2012, 02:57 PM
  3. Replies: 5
    Last Post: 03-16-2010, 07:05 AM
  4. Formula Help with Pass/Fail function
    By Spyroviper in forum Excel General
    Replies: 3
    Last Post: 02-04-2009, 01:02 PM
  5. [SOLVED] pass fail
    By Tricia in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 01-20-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1