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!
Bookmarks