Complicated formula nesting issue in Google Spreadsheets

Question

I'm currently trying to develop a complicated formula for a work project and I've hit a roadblock. This spreadsheet tracks people’s physical performance in 5 specific areas during a physical performance screening test that I administer once a month. I'll explain the end goal, show examples of what has worked and what has not, and provide a link to a sample spreadsheet.

The end goal. I need a cell to display "Pass" or "Fail" based on five other cells meeting a minimum criteria, and the minimum criteria for those five cells needs to vary based on the value of another cell. On top of that, I need the color of the 5 cells to vary (red, yellow, green) based on 3 defining metrics (fail, caution, and good)

Here it is in a more specific set of terms. I have five jobs available, we will label them as follows (SO, SB, ND, EOD, AW). Each job requires its potential candidate to perform the physical screening test (PST) and meet certain minimum scores in order to be considered for the job. There are 3 ranges of scores for each job, each resulting in the cell being colored red, green, or yellow. If the candidate gets a red score in any of the 5 categories then he gets a “Fail”.

Here is my sample spreadsheet (let me know if it does not work) where you can find all of my attempts at making this work.

Column "I" is where the formula needs to go.

Cell "I:3" has the version that works with a single set of parameters, however, I am stuck when trying to add more parameters.

=IF((COUNTIF(D3,"<9:31")+COUNTIF(E3, ">65")+COUNTIF(F3, ">65")+
COUNTIF(G3,">11")+COUNTIF(H3,"<10:01"))=5,"P","F")

Cell "I:4" has my latest attempt at nesting the formula but I just feel like there's an easier way. I can do basic "if/and" formulas, but I think I'm way over my head on this one.

=COUNTIFS(B3=”SO”,=IF((COUNTIF(I4,ʺ<12:31ʺ)+COUNTIF(J4,ʺ>50ʺ)+
COUNTIF(K4,ʺ>50ʺ)+COUNTIF(L4,ʺ>10ʺ)+COUNTIF(M4,ʺ<10:31ʺ))=5,ʺPʺ,ʺFʺ),
IF(B3=”SB”,=IF((COUNTIF(I4,ʺ13:01ʺ)+COUNTIF(J4,ʺ>50ʺ)+COUNTIF(K4,ʺ>50ʺ)+
COUNTIF(L4,ʺ>6ʺ)+COUNTIF(M4,ʺ<12:01ʺ))=5,ʺPʺ,ʺFʺ),IF(B3=”EOD”,
=IF((COUNTIF(I4,ʺ<12:31ʺ)+COUNTIF(J4,ʺ>50ʺ)+COUNTIF(K4,ʺ>50ʺ)+
COUNTIF(L4,ʺ>6ʺ)+COUNTIF(M4,ʺ<12:31ʺ))=5,ʺPʺ,ʺFʺ),IF(B3=”ND”,
=IF((COUNTIF(I4,ʺ<12:31ʺ)+COUNTIF(J4,ʺ>50ʺ)+COUNTIF(K4,ʺ>50ʺ)+
COUNTIF(L4,ʺ>6ʺ)+COUNTIF(M4,ʺ<12:30ʺ))=5,ʺPʺ,ʺFʺ),IF(B3=”AW”,
=IF((COUNTIF(I4,ʺ<12:01ʺ)+COUNTIF(J4,ʺ>42ʺ)+COUNTIF(K4,ʺ>50ʺ)+
COUNTIF(L4,ʺ>4ʺ)+COUNTIF(M4,ʺ<12:01ʺ))=5,ʺPʺ,ʺFʺ))))))

I have listed the needed parameters in the sample spreadsheet as well.

Note: There is a followup question here

Answer 1

Partial short answer

The formula in I4 has some syntax errors

• Use of the wrong quotation character
• Use of equal sign after commas

Also, the formulas in I3 and I4 have some programming errors:

• Wrong references
• "Unnecessary complexity"

Explanation

Syntax errors

Quotation marks

Modern applications use a large number of characters some of them are visually very similar like "(U+0022 QUOTATION MARK), ” (U+201D RIGHT DOUBLE QUOTATION MARK) and ʺ(U+02BA MODIFIER LETTER DOUBLE PRIME) but for technical purposes, like enclosing strings in spreadsheet formulas, " (U+0022 QUOTATION MARK) should be used. It's worth to say that some text processors like Microsoft Word could automatically replace " by ” so when using and external application as a helper to write complex formulas the related feature should be turned off.

Equal sign

The equal sign could be used as an operator in spreadsheet formulas at the beginning of a formula or as a comparison operator, but not after the function arguments separator (comma or semicolon, according to the spreadsheet locale settings)

Programming errors

Yes, writing a spreadsheet formula makes people to be programmers but usually they are called analysts or just spreadsheet users.

Wrong references

The formula in I4 include references to columns that don't exists (M4,K4,S4,L4)

Unnecessary complexity

The formulas in I3 and I4 use COUNTIF to do a comparisons and the add operator to check if all the required conditions are met. The same could be done in a simpler way by using comparison operators and the boolean functions. While complex ways to do things could work, when something fails, one thing to try is to reduce the complexity.

The following formula is equivalent to the I3 formula but "simpler" (from a "programming" point of view):

=IF(AND(D3<"9:31",E3>65,F3>65,G3>11,H3<"10:01"),"P","F")