Trying to figure out nested IF AND OR statement... please help!

[delacruza18]

Hello... I work as a Quality Assurance Analyst and I am needing assistance with coming up with a very complicated nested formula. I am creating an "accountability" track sheet for Agent's audit scored for each month.

Here is our workflow with their Supervisors/Management:
- Agent's monthly score fails to meet minimum standard (90%)
- 1:1 Coaching is provided by Supervisor the next month
- Agent's monthly score fails to meet minimum standard (90%) within 6 months
- 1:1 Coaching is provided by Quality Analyst
- If Agent fails the next month after being trained by the Quality Analyst, Supervisor/Manager takes disciplinary action.

I posted an example of what this would look like as an attachment. Basically, I am trying to figure out a formula that will take into consideration their scored for a rolling 6 month period... Keep in mind that the tracking would need to "reset" after the Agent meets a 90% or higher for a month completed... IS THIS EVEN POSSIBLE?!

 

[J.Ty.]

Please try the follwing formula. It evaluates to TRUE if there is an event like you describe: two 90%- events in a row, preceded by another 90%- event at most 6 months before. It does not check if coaching took place. You can test it here.

J.Ty.

Book1
	A	B	C
1	88.00%		True
2	91.00%
3	99.00%
4	90.00%
5	96.00%
6	88.00%
7	90.00%
8	99.00%
9	89.00%
10	88.00%
11	96.00%
12	99.00%
13	94.00%
14	80.00%
15	98.00%
16	98.00%
17	95.00%
18	94.00%
19	89.00%
20	90.00%
21	90.00%
22	90.00%
23	90.00%
24	90.00%
25	90.00%
26	90.00%
27	90.00%
Sheet1

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(($A$6:$A$25<0.9)*($A$7:$A$26<0.9)*(($A$1:$A$20<0.9)+($A$2:$A$21<0.9)+($A$3:$A$22<0.9)+($A$4:$A$23<0.9)+($A$5:$A$24<0.9)))>0