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

1. ## 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!

2. ## 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

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

hi benishiryo,

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

regards,
sathya

4. ## 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")

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

Originally Posted by sathiyamoorthy
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. ## 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. ## 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"))

8. ## 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. ## Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

Bump no response

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

Originally Posted by L-Drr
=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. ## Re: Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is

Thanks very much

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

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