Conditional Formatting with formula

[sradkovich]

I am trying to establish a formula (im thinking its best fit is in conditional formatting) that will cause Excel to higlight the values which occur most often within a column (or better yet -variable colors from red to yellow red being the most often and yellow being the least often).

So for example:

Row ___________
1 | Alan |
2 | Alan |
3 | Alan |
4 | Alan |
5 | Alan |
6 | Bob |
7 | Bob |
8 | Bob |
9 | Craig |
10 | Craig |


Since "Alan" occurs 5 times, it would hilight Alan in red,
Bob (3 times) - Orange
Craig (twice) - Yellow

This would also update as i add to the rows, so if, overtime, i add Craig 5 more times in rows 11-15 - Craig would end up being Red.

Thanks in advance.

 

[Matty]

What should happen in the case of a draw, i.e. two names that occur the same number of times?

Matty

 

[abmati]

For Range A1:A1000, use the following formula in A1 cell for conditional formatting.
Then copy A1 to A1000 with Paste Special and Format.
=COUNTIF($A$1:A1,A1)+COUNTIF(A2:$A$1000,A1)=5 --> Red
=COUNTIF($A$1:A1,A1)+COUNTIF(A2:$A$1000,A1)=3 --> Orange
=COUNTIF($A$1:A1,A1)+COUNTIF(A2:$A$1000,A1)=2 --> Yellow

Regards,

 

[sradkovich]

thanks abmati but the formula needs to be dynamic, so as the numbers increase i would like the formula to continue to capture which is the highest, second highest and third highest in count. I believe your formul would only work for if it equals the static number (5,3,2).

Conditional formatting can currently capture the highest numbers but it would seem logical that they would also build in something to allow for the highest number of occurances as well.

Matty, The draw would result in both numbers being the same color, but again, the formula would essentially rear like, highest count in column A is formatted as red, the second highest count in column A is formatted as orange, and the 3rd highest count in column A is formatted as orange.


The preset conditional formatting allows for standard deviations from the average which gets kind of close to what im looking for but i cant get the second hightest and third highest to work consistantly.

 

[Matty]

Using a Conditional Formatting formula, try...

For max:

=COUNTIF(A$1:A$10,A1)=MAX(COUNTIF(A$1:A$10,A$1:A$10))

With red fill.

For min:

=COUNTIF(A$1:A$10,A1)=MIN(COUNTIF(A$1:A$10,A$1:A$10))

With yellow fill.

All other values should be in between, so these can be permanently filled with orange (the Conditional Formatting will then over-colour if a condition is met).

To make thing dynamic, use a dynamic Named Range. Plenty on this board about them if you do a search.

Matty

 

[sradkovich]

THANKS - your awesome!

I got the Max to work but the MIN wasnt working over the Orange. Ill try to keep playing with it and see if i can get it to over shadow but for now i can work with MAX....

I wish i could have found this without having to create a thread which may have already been covered, the only problem is that i would have never thought to search for Named Range and considering the nature of the problem (typically trying to solve an excel question quickly) its hard to spend the time necessary to find these answers.

Thanks again, a huge help!

 

[Matty]

THANKS - your awesome!

I got the Max to work but the MIN wasnt working over the Orange. Ill try to keep playing with it and see if i can get it to over shadow but for now i can work with MAX....

I wish i could have found this without having to create a thread which may have already been covered, the only problem is that i would have never thought to search for Named Range and considering the nature of the problem (typically trying to solve an excel question quickly) its hard to spend the time necessary to find these answers.

Thanks again, a huge help!

Glad I could help. And let me know if you need any helps with the dynamic Named Ranges.

Matty