Conditional formatting on Consecutive cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

welshobit - 20 Jun 2006 10:34 GMT

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?

Signature

welshobit

Reply to this Message

Toppers - 20 Jun 2006 11:21 GMT

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

> Help! :confused:
>
[quoted text clipped - 6 lines]
>
> Can this be done?

Reply to this Message

Bob Phillips - 20 Jun 2006 11:33 GMT

Is it just those three columns, if so

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

Signature

HTH

Bob Phillips

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

> Help! :confused:
>
[quoted text clipped - 6 lines]
>
> Can this be done?

Reply to this Message

welshobit - 20 Jun 2006 13:24 GMT

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?

Signature

welshobit

Reply to this Message

Toppers - 20 Jun 2006 15:58 GMT

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

> Thanks both,:)
>
[quoted text clipped - 3 lines]
> below over 3 consecutive weeks say at weeks 5,6,7 how would it work
> then?

Reply to this Message