Complicated formula nesting issue in Google Spreadsheets pt2

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 five 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 five cells to vary (red, yellow, green) based on three 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 three 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 five categories then he gets a Fail.

UPDATE Partial success. With much help the formula that displays the data is spot on now. (Thanks Rubén.) The completed formula can be found in the sample spreadsheet linked below.

The last part is how to get the conditional formatting (cell color) of the data fields to change based on not just one but five different parameters. I have one figured out with using the built in formula builder in the conditional formatting tab of Google Sheets but in order to make it work across five separate fields I'm afraid it will need to be a custom formula.

Here is my example spreadsheet. Please reference Complicated formula nesting issue in Google Spreadsheets.

Answer 1

The formula used to correctly display the data is as follows:

=IF(B3=ʺEODʺ,IF(AND(D3<ʺ12:30ʺ,E3>50,F3>50,G3>6,H3<ʺ12:30ʺ),ʺPʺ,ʺFʺ),IF(B3=ʺSOʺ,IF(AND(D3<ʺ12:30ʺ,E3>50,F3>50,G3>10,H3<ʺ10:30ʺ),ʺPʺ,ʺFʺ),IF(B3=ʺSBʺ,IF(AND(D3<ʺ13:00ʺ,E3>50,F3>50,G3>6,H3<ʺ12:00ʺ),ʺPʺ,ʺFʺ),IF(B3=ʺNDʺ,IF(AND(D3<ʺ12:30ʺ,E3>50,F3>50,G3>6,H3<ʺ12:30ʺ),ʺPʺ,ʺFʺ),IF(B3=ʺAWʺ,IF(AND(D3<ʺ12:00ʺ,E3>42,F3>50,G3>4,H3<ʺ12:00ʺ),ʺPʺ,ʺFʺ))))))

It should be noted that the formula was not typed by me personally, it was added to a copy of my sample spreadsheet. When attempting to copy and paste from the copy into my spreadsheet, there was an unknown error with the transfer and although the formula appeared to be written identically, it would not work.

I hand typed it character by character and it then worked correctly.
I don't have an explanation for this but should anyone run into a similar issue, there's the fix.