Conditional formatting on Consecutive cells

W

welshobit

Help! :confused:

I’m looking to create a conditional format on a cell which states an
agent’s name that will flag Red! Sounds easy yes! however I need it to
flag red only if they have three consecutive values in different
columns but in the same row i.e in cell a2 value =below, in cell b2
value=below and in cell c2 value = below. If the sequence went below,
above, below then don’t flag red.

Can this be done?
 
T

Toppers

Do you mean?

Highlight required column then in Conditional Formatting:

Formula is:

=AND(A1=value,B1=value,C1=value,A1<>"",B1<>"",C1<>"")

Set colour format

Click OK
 
B

Bob Phillips

Is it just those three columns, if so

=COUNTIF(A2:C2,"value")=3

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
W

welshobit

Thanks both,:)

Ok both work in their own right great so thanking you kindly however,
lets say the example i gave earlier was based on an agents productivity
score based over a 12 week period and within that period they score a
below over 3 consecutive weeks say at weeks 5,6,7 how would it work
then?
 
T

Toppers

A (not very elegant) possible solution:

Assumes the 12 values are in columns B to M.

In a spare column put this formula and copy down:

=IF(ISERROR(MATCH("below",$B2:$M2,0)),"",IF(AND(OFFSET($B2,0,MATCH("below",$B2:$M2,0))="below",OFFSET($B2,0,MATCH("below",$B2:$M2,0)+1)="below"),1,0))

If three consecutive values of "below" are found, it puts 1 in the column or
if less than 3, then 0. If there are no "below" ratings it is left blank.

You could then set your conditional formatting based on the value 1 in this
column.

HTH
 
Top