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

delacruza18

New Member
Joined
Feb 3, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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
    2020-02-03_8-10-17.png
    9.7 KB · Views: 14
  • 2020-02-03_8-17-02.png
    2020-02-03_8-17-02.png
    39.1 KB · Views: 14

Some videos you may like

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
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,193
Messages
5,546,481
Members
410,742
Latest member
WalterSil
Top