3

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

  • The title is too generic. Please make it more specific. – Rubén Aug 31 '16 at 3:05
  • Is that what you were looking for or something different? – Cory Hooper Aug 31 '16 at 13:11
  • Thanks for cleaning that up, Ruben. I'm not the most computer literate (obviously) but I'm trying – Cory Hooper Aug 31 '16 at 13:33
  • I just posted a partial answer as there are several things to fix. Please change the sharing permissions of the demo spreadsheet to view instead of edit. If you will change something, do that on a copy of the spreadsheet. You could ask for clarifications but if will do a follow up question, please post it as a new question including a link to this one. – Rubén Aug 31 '16 at 15:00
  • I finally got around to making these fixes. New question has been asked. Thank you for the help – Cory Hooper Sep 8 '16 at 1:30
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")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.