Counting and colour coding ranges of occurances

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ConfusedinCalgary - 18 Jul 2006 16:55 GMT

a have a list of employee ID numbers and need to count how many times they
occur withing certain ranges. For example: if one occurs between 5-10 times
I want all occurances highlighted in yellow, if it occurs between 10-15 times
I want all occurances highlighted in blue, and if it occurs more than 15
times I want all occurances highlighted in red. Any suggestions?

Reply to this Message

Alan - 18 Jul 2006 17:59 GMT

You can use conditional formatting using these formulas for the three
conditions.

=AND(COUNTIF($A$1:$A$30,A1)>5,COUNTIF($A$1:$A$30,A1)<11)
=AND(COUNTIF($A$1:$A$30,A1)>10,COUNTIF($A$1:$A$30,A1)<16)
=COUNTIF($A$1:$A$30,A1)>15

Regards,
Alan.

>a have a list of employee ID numbers and need to count how many times they
> occur withing certain ranges. For example: if one occurs between 5-10
[quoted text clipped - 3 lines]
> I want all occurances highlighted in blue, and if it occurs more than 15
> times I want all occurances highlighted in red. Any suggestions?

Reply to this Message

ConfusedinCalgary - 18 Jul 2006 18:54 GMT

I still can't get it to work. I know it is obviously operator error but
still need some help. My data is in column C which grows longer every day.
Every conditional format I try recognizes the first condition but not the
second and third. For example a number may appear 12 times but it still
codes with the color from 5-10. What am I doing wrong?

> You can use conditional formatting using these formulas for the three
> conditions.
[quoted text clipped - 13 lines]
> > I want all occurances highlighted in blue, and if it occurs more than 15
> > times I want all occurances highlighted in red. Any suggestions?

Reply to this Message

Alan - 18 Jul 2006 19:45 GMT

Try using Conditional Formatting in C1 using 'Formula Is' with these three
formulas in the three conditions, then highlight the whole of column C and
go to Conditional Formatting again, OK
=AND(COUNTIF(C:C,C1)>5,COUNTIF(C:C,C1)<11)
=AND(COUNTIF(C:C,C1)>10,COUNTIF(C:C,C1)<16)
=COUNTIF(C:C,C1)>15
Regards,
Alan.

>I still can't get it to work. I know it is obviously operator error but
> still need some help. My data is in column C which grows longer every
[quoted text clipped - 22 lines]
>> > 15
>> > times I want all occurances highlighted in red. Any suggestions?

Reply to this Message

Alan - 19 Jul 2006 00:38 GMT

The first formula should be:
=AND(COUNTIF(C:C,C1)>4,COUNTIF(C:C,C1)<11)

>4, not >5

REgards,

> Try using Conditional Formatting in C1 using 'Formula Is' with these three
> formulas in the three conditions, then highlight the whole of column C and
[quoted text clipped - 32 lines]
>>> > 15
>>> > times I want all occurances highlighted in red. Any suggestions?

Reply to this Message

Rate this thread: