Conditional Formatting

[Adore195]

I have a spread sheet that contains monthly scores for my Reps. The score scale is 1-5.
Below is the criteria:

If an agent get a score lower than 3 for 3 consecutive months then they will receive a written
If an agent get a score lower than 3 for 4 consecutive months then they will receive a Final
If an agent get a score lower than 3 for 5 consecutive months then they will be terminated

Agent

Supervior

January

February

March

April

May

June

July

August

September

October

November

December

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

Agent 2

3

3

3

2

5

5

5

2

5

5

5

5

Agent 3

Agent 4

Agent 5

Agent 6

Agent 7

Agent 8

Agent 9

Agent 10

Agent 11

Agent 12

Agent 13

<colgroup><col><col><col span="12"></colgroup><tbody>
</tbody>

I want to be able to conditionally format this with the count if formula but am stuck.

 

[keithmct]

Welcome to the board,
could you just conditionally format the cells so numbers less than 3 are filled in red and if 3 consecutive cells are red it would be evident?

 

[etaf]

you could try an array formula

https://www.mrexcel.com/forum/excel-questions/976241-count-consecutive-cells-above-below-value.html

=MAX(FREQUENCY(IF(B6:N6<P1,COLUMN(B6:N6)),IF(B6:N6>=P1,COLUMN(B6:N6))))>=3
using control + shift + enter
to get {}
{=MAX(FREQUENCY(IF(B6:N6<P1,COLUMN(B6:N6)),IF(B6:N6>=P1,COLUMN(B6:N6))))>=3}

P1 has the values to look for consecutive values

 

[etaf]

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Value

3

3

3

2

3

Consecutive

4

Agent

Supervior

January

February

March

April

May

June

July

August

September

October

November

December

3

4

5

5

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

QA Score

6

Agent 2

23

2

4

2

4

2

2

2

2

2

4

4

5

TRUE

TRUE

TRUE

7

Agent 3

8

Agent 4

written

Final

termination

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:91px;"><col style="width:64px;"><col style="width:57px;"><col style="width:90px;"><col style="width:81px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula P6 {=MAX(FREQUENCY<span style=" color:008000; ">(IF<span style=" color:#0000ff; ">(B6:N6<p1,column(B6:N6)</p1,column),IF(B6:N6>=P1,COLUMN(B6:N6))))>=3} Q6 {=MAX(FREQUENCY<span style=" color:008000; ">(IF<span style=" color:#0000ff; ">(B6:N6<q1,column(B6:N6)</q1,column),IF(B6:N6>=Q1,COLUMN(B6:N6))))>=4} R6 {=MAX(FREQUENCY<span style=" color:008000; ">(IF<span style=" color:#0000ff; ">(B6:N6<r1,column(B6:N6)</r1,column),IF(B6:N6>=R1,COLUMN(B6:N6))))>=5} P8 =IF(P6,"written","") Q8 =IF(Q6,"Final","") R8 =IF(R6,"termination","") <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[etaf]

actually in P1 you need a 4
to count a consecutive series of

3,2,1 etc