Redundent values

[Bojames]

How do I highlight the common values in a range containing 5 columns and
several rows? I would like to group it with the 5 most common is one color
and the 5 next most common another color. Is there a way to use excel for
this?

 

[Mike H]

Hi,

Try this for a data range of A1 - E3 and I have assumed your data are numbers

This formula in G1 to get the most common
=MODE(A1:E3)

This formula in H1 to get the second most common
=MODE(IF(($A$1:$E$3)*($A$1:$E$3<>G1),$A$1:$E$3))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Now select you data range and add 2 conditional formats


Cell value - is equal to =$G$1 and pick a colour
Cell value - is equal to =$H$1 and pick another colour

Mike

 

[Stefi]

Hi Mike,

I wonder that Bojames meant MODE as most common, I think most common is the
most frequent.

If so, I'd put
=LARGE(COUNTIF($A$2:$E$6,A2),1)
as an array formula in G1

The only problem is that I couldn't figure out the formula for the second
most frequent.

What is your opinion? Do you have a solution for the second most frequent
number?

Regards,
Stefi

„Mike H†ezt írta:

 

[Mike H]

Stefi,

You may be right about the most frequent. You fomula isn't an array and
like you I can't get the second most common out of it, I think I resort to VB
to do that. Now that means someone is going to post a formula solution!!

Mike

 

[Bojames]

My question was. How do I use excel to display the 10 most common values. I
would like them grouped into 5's (i.e. by color).

Basically, I have a range from H2 to L1000. I need to know the 5 most common
in that range in the color green. The second 5 most common in the color red.
Preferably also displayed in a another row grouped by order of most common.