Formula Trouble

[janetm126]

I have a range of cells (C5:C31) in which a numeric value can be placed.  If 3 or more of these cells contain >=20, I want another cell to return "Warning".  I am far from an expert, but I've tried all I can do to figure it out.  Would someone please tell me how to write this dang formula? Thank you!

[#2 · Accepted solution]

@janetm126 

=IF(COUNTIF($C$5:$C$31,">="&20)>=3,"Warning","")

You can try this formula.

 

[#3]

>3! Thank you! I could not figure out how to add that in! I appreciate you so much.

[#4]

@OliverScheurich, what if I needed an additional warning on top of the warning I already have? If C1:C31 >=100 AND E14=Warning, return Super Warning in another cell. How can I say IF AND when I am relying on the results of another formula (E14)?

[#5]

@janetm126 

Does the formula in the attached file return the expected result?

[#6]

@OliverScheurich 

Not quite.  I am tracking monetary losses for a month's time and the disciplinary action for each loss category: cumulative amounts, large amounts, and subsequent large amounts. C5:C35 represents the 1st - 31st.

Employee receives disciplinary action for losses > 20*3 in the month.  Cell 47 has the formula you provided (thank you!): =IF(COUNTIF($C$5:$C$35,">="&20)>=3,"Coach","")

Employee receives disciplinary action for a any loss of > 250. Cell C48 has the formula:  =IF(COUNTIF($C$5:$C$35, ">="&250)>=1, "Warning", " ")

So, now, if the employee has the "Warning" in cell C48 AND they have another loss of > 100, I need cell C49 to return "Super Warning."  I hope these details make my request clearer.

P.S. Column C is for January.  I need the formulas to work for every other month as well.  Will the $ in my existing formulas cause issues since it's anchored?

[#7]

Tried your formula again and it does work! But it only references one cell being >= 100. How can I make it apply to C5:C35?

[#8]

@janetm126 

=IF(AND(C$48="Warning",COUNTIF(C$5:C$35,">="&100)>=2),"Super Warning","")

This is the formula in cell C49 for january. I've changed the $ references to C$5:C$35 and copied the formula across the range C49:N49 for all months.

The formulas for "Coach" and "Warning" are adapted in the same way in the attached file.

[#9]

You are AMAZING!!!!!

[#10]

So, let me ask you one last thing. 🙂 If I have... =IF(AND(C$48="Warning",COUNTIF(C$5:C$35,">="&100)>=2),"Super Warning","")
...and I change the >=2 to =1 (so only one additional 100 loss gets the super warning), can I add in another logical test that says if a second 100 loss occurs (>=2?) returns "Youre Out"?

[#11]

@janetm126 

The >=2 in the "Super Warning" formula means that there is only one additional 100 loss (at least). This is because of the 250 loss from the "Warning" formula. This 250 loss is at the same time a 100 loss. Another 100 loss would mean >=2 in the "Super Warning" formula. I'd use >=2 instead of =2 in order to return the "Super Warning" for any number of additional 100 losses.

=IF(AND(C$48="Warning",COUNTIF(C$5:C$35,">="&100)>=3),"You're out","")

I've entered this formula for "You're out" in the attached file.

For verification i've entered losses of 252, 117 and 125 step by step for december in order to check the results of the formulas and it seems to work (i didn't save these entries).

I don't want to complicate things but i was wondering if you want to return any message if there are e.g. 3 losses of 100 and none of these is a 250 loss. 

[#12]

@janetm126 

=IF(AND(COUNTIF(C$5:C$35,">="&250)>=1,COUNTIF(C$5:C$35,">="&100)>=3),"You're out",IF(AND(COUNTIF(C$5:C$35,">="&250)>=1,COUNTIF(C$5:C$35,">="&100)>=2),"Super Warning",IF(COUNTIF(C$5:C$35,">="&250)>=1,"Warning",IF(COUNTIF(C$5:C$35,">"&20)>3,"Coach",""))))

I've combined all formulas in cell C52 and copied across range C52:N52.

[#13]

That is certainly a flaw in the "policy" as I should have a message for that too!

[#14]

You deserve to be Quintuple_Pawn or Quadruple_King for all your help. You have been amazing. I hope to show you my skill set in the future as I get better and better.

[#15]

@OliverScheurich I'm stuck again. 😞

Here is my formula in cell C47:
=IF(AND(COUNTIF(E$5:E$35,">="&20)>=5),"Possible Documented Coaching",IF(AND(COUNTIF(E$5:E$35,">"&250)>=1),"Documented Verbal Warning",IF(AND(COUNTIF(E$5:E$35,"="&250),"Documented Verbal Warning", " " ))

I'd like to add a third component, if it's even possible. If the cumulative amounts in cells C5:C35 is >= $250.00, I'd like "Documented Verbal Warning display as well. I can't figure it out, as I assumed it would be a SUM.

[#16]

@janetm126 

=IF(COUNTIF(C$5:C$35,">="&20)>=5,"Possible Documented Coaching",IF(COUNTIF(C$5:C$35,">"&250)>=1,"Documented Verbal Warning",IF(SUM(C$5:C$35)>=250,"Documented Verbal Warning","")))

Maybe with this formula which i've entered in cell C47 and copied across range C47:N47.

[#17]

What would I do without you? This is perfect...again.

[#18]

@OliverScheurich 

I give up. Just when I think I have it figured out, I don't. These are the levels of counseling an employee receives that line up with the formulas you've written:

Unresolved Outage Thresholds                              Possible Corrective Action
5 or more outages > $20 in a calendar month      Documented Coaching Session
Single or Cumulative Outages $250 or more         1st Violation- Documented Verbal Warning
Subsequent Outage(s) Totaling $100 or more       2nd Violation- Documented Written Warning
Subsequent Outage(s) Totaling $100 or more       3rd Violation - Termination Review

Do I need four separate formulas? If I log $100 fives time in column C, it will revert back to "Coaching" since it meets that criteria too. HELP.  I've attached my spreadsheet.

[#19]

@janetm126 

=IF(AND(COUNTIF(E$5:E$35,">="&250)>=1,COUNTIF(E$5:E$35,">="&100)>=3),"Termination Review",IF(AND(COUNTIF(E$5:E$35,">="&250)>=1,COUNTIF(E$5:E$35,">="&100)>=2),"Documented Written Warning",IF(OR(COUNTIF(C$5:C$35,">="&250)>=1,SUM(E$5:E$35)>=250),"Documented Verbal Warning",IF(COUNTIF(C$5:C$35,">="&20)>=5,"Possible Documented Coaching",""))))

I've tried to combine all levels in this formula in row 50. I've corrected the formula in row 47 which was wrong unfortunately. It has to consider "Documented Verbal Warning" in the first step and "Possible Documented Coaching" in the second step.

However i am unsure if i exactly understand your sheet. For example there are 3 columns for every month and the formulas refer to 2 different columns (columns C and E for january).

I can't copy the formulas across the rows because of the merged cells. I'd recommend to remove all the merged cells and never working with merged cells as they often cause problems especially when working with formulas.   

[#20]

Column E, as I'm sure you can see, is the net difference of C and D. If the associate recovers their money (opposite entry in D than C), it does not count against them. Does that make a difference in what you've formulated?