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.
=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"))))))))))