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.

• Check if this works correctly `=IF(B3="EOD",IF(AND(D3<"12:30",E3>50,F3>50,G3>6,H3<"12:30"),"P", IF(B3="SO",IF(AND(D3<"12:30",E3>50,F3>50,G3>10,H3<"10:30"),"P", IF(B3="SB",IF(AND(D3<"13:00",E3>50,F3>50,G3>6,H3<"12:00"),"P", IF(B3="ND",IF(AND(D3<"12:30",E3>50,F3>50,G3>6,H3<"12:30"),"P", IF(B3="AW",IF(AND(D3<"12:00",E3>42,F3>50,G3>4,H3<"12:00"),"P","F"))))))))))`
– Rubén
Sep 8 '16 at 1:46
• Negative, still getting a formula parse error Sep 8 '16 at 1:51
• Something weird is happening with the copy/paste process
– Rubén
Sep 8 '16 at 2:06
• Check the formula on I4 on this file (is the same from the previous comment)
– Rubén
Sep 8 '16 at 14:16
• Edits should not change questions entirely. They should focus on make the question clearer but also you could add notes regarding your progress to find the solution when they are small. If you think that making minor edits will be complicated, then post a new question. By the way, if you already found a significant progress, you can post it as an answer.
– Rubén
Sep 8 '16 at 15:14

``````=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ʺ))))))