Jun 03 2022 12:28 PM
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!
Jun 03 2022 12:40 PM
SolutionJun 03 2022 01:24 PM
Jun 07 2022 02:33 PM
Jun 07 2022 02:58 PM
Jun 08 2022 09:19 AM
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?
Jun 08 2022 10:39 AM
Jun 08 2022 10:52 AM
=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.
Jun 08 2022 12:56 PM
Jun 08 2022 01:44 PM
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.
Jun 08 2022 02:01 PM
=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.
Jun 08 2022 03:34 PM
Jun 08 2022 03:37 PM
Jul 11 2022 09:57 AM
Jul 11 2022 02:06 PM
=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.
Jul 19 2022 06:41 AM
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.
Jul 20 2022 01:20 AM
=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.
Jul 21 2022 09:27 AM
Jun 03 2022 12:40 PM
Solution=IF(COUNTIF($C$5:$C$31,">="&20)>=3,"Warning","")
You can try this formula.