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

[TABLE="width: 1195"]
<colgroup><col><col><col span="12"></colgroup><tbody>[TR]
[TD="align: center"]Agent [/TD]
[TD="align: center"]Supervior [/TD]
[TD="align: center"]January[/TD]
[TD="align: center"]February[/TD]
[TD="align: center"]March[/TD]
[TD="align: center"]April[/TD]
[TD="align: center"]May[/TD]
[TD="align: center"]June[/TD]
[TD="align: center"]July[/TD]
[TD="align: center"]August[/TD]
[TD="align: center"]September[/TD]
[TD="align: center"]October[/TD]
[TD="align: center"]November[/TD]
[TD="align: center"]December[/TD]
[/TR]
[TR]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score
[/TD]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score [/TD]
[TD="align: center"]QA Score [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 2[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"] 5[/TD]
[TD="align: center"]5 [/TD]
[TD="align: center"] 5[/TD]
[TD="align: center"]5 [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 3[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 4[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 5[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 6[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 7[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 8[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 9[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 10[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 11[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 12[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Agent 13[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]

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

Value

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="colspan: 3, align: center"]Consecutive[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]Agent[/TD]
[TD="align: center"]Supervior[/TD]
[TD="align: center"]January[/TD]
[TD="align: center"]February[/TD]
[TD="align: center"]March[/TD]
[TD="align: center"]April[/TD]
[TD="align: center"]May[/TD]
[TD="align: center"]June[/TD]
[TD="align: center"]July[/TD]
[TD="align: center"]August[/TD]
[TD="align: center"]September[/TD]
[TD="align: center"]October[/TD]
[TD="align: center"]November[/TD]
[TD="align: center"]December[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]
[TD="align: center"]QA Score[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]Agent 2[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]Agent 3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]Agent 4[/TD]

[TD="align: center"]written[/TD]
[TD="align: center"]Final[/TD]
[TD="align: center"]termination[/TD]

</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