Most and least common

  • Thread starter Thread starter jmj713
  • Start date Start date
J

jmj713

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.
 
Assuming you have your values in Col A
then type this in B1
=INDIRECT("A"&MATCH(MAX(COUNTIF(A1:A30,A1:A30)),COUNTIF(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)),COUNTIF(A1:A30,A1:A30),0))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Most commonly dialled extensions 4
Compare texts in Excel 2003 2
Second Most Common Value 5
Excel How to find the most common pairs and triplets numbers? 5
Formula help 10
How to add numbers in adjacent cells 2
date and time 1
columns to rows 1

Back
Top