| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Counting and colour coding ranges of occurances
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? |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Counting and colour coding ranges of occurances
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,A 1)<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. "ConfusedinCalgary" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Counting and colour coding ranges of occurances
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? "Alan" wrote: 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,A 1)<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. "ConfusedinCalgary" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Counting and colour coding ranges of occurances
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. "ConfusedinCalgary" wrote in message ... 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? "Alan" wrote: 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,A 1)<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. "ConfusedinCalgary" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Counting and colour coding ranges of occurances
The first formula should be:
=AND(COUNTIF(C:C,C1)4,COUNTIF(C:C,C1)<11) 4, not 5 REgards, Alan. "Alan" wrote in message ... 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. "ConfusedinCalgary" wrote in message ... 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? "Alan" wrote: 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,A 1)<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. "ConfusedinCalgary" wrote in message ... 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting cells according to their colour | Excel Discussion (Misc queries) | |||
| Counting colour formatted cells | Excel Worksheet Functions | |||
| Counting cells with a specific background colour | Excel Discussion (Misc queries) | |||
Linear Mode
