#### delacruza18

##### New Member
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?!

#### Attachments

• 2020-02-03_8-10-17.png
9.7 KB · Views: 14
• 2020-02-03_8-17-02.png
39.1 KB · Views: 14

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### J.Ty.

##### Well-known Member
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
ABC
188.00%True
291.00%
399.00%
490.00%
596.00%
688.00%
790.00%
899.00%
989.00%
1088.00%
1196.00%
1299.00%
1394.00%
1480.00%
1598.00%
1698.00%
1795.00%
1894.00%
1989.00%
2090.00%
2190.00%
2290.00%
2390.00%
2490.00%
2590.00%
2690.00%
2790.00%
Sheet1
Cell Formulas
RangeFormula
C1C1=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

Replies
2
Views
55
Replies
3
Views
390
Replies
3
Views
321
Replies
17
Views
1K
Replies
1
Views
2K