Most and least common
 

I need to find both the most and the least common text values in a column.
For instance, if I have ten entries reading "Tuesday" and three "Monday", I'd
like to be able to return "Tuesday" as the most common and "Monday" as the
least common value.

Most and least common
 

Assuming you have your values in Col A
then type this in B1
=INDIRECT("A"&MATCH(MAX(COUNTIF(A1:A30,A1:A30)),CO UNTIF(A1:A30,A1:A30),0))
and
IMP:- press CTRL-SHIFT-ENTER as this is an ARRAY formula, to get the Max
entry..

and this for MIN in B2
=INDIRECT("A"&MATCH(MIN(COUNTIF(A1:A30,A1:A30)),CO UNTIF(A1:A30,A1:A30),0))

-------------------------------------
Pl. click ''Yes'' if this was helpful...



"jmj713" wrote:

I need to find both the most and the least common text values in a column.
For instance, if I have ten entries reading "Tuesday" and three "Monday", I'd
like to be able to return "Tuesday" as the most common and "Monday" as the
least common value.

Most and least common
 

Let's do this the easy way:

Assume your range of data is A1:A10
Enter this formula in B1 and copy down to B10:

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

For the most common item:

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0))

For the least common item:

=INDEX(A1:A10,MATCH(MIN(B1:B10),B1:B10,0))

If there are more than one most/least common item the formula returns the
*first* instance from top to bottom.

--
Biff
Microsoft Excel MVP


"jmj713" wrote in message
...
I need to find both the most and the least common text values in a column.
For instance, if I have ten entries reading "Tuesday" and three "Monday",
I'd
like to be able to return "Tuesday" as the most common and "Monday" as the
least common value.