most common data

  • Thread starter Thread starter The Rook
  • Start date Start date
T

The Rook

I have column A consists of employees names obviously in text format.

Is it possible to say have cell B1 populated with the name that aears the
most?
 
Hi,

Try this ARRAY formula. See below on how to enter it and adjust the range to
suit.

=INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),COUNTIF(A1:A20,A1:A20),0))

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.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
1) Name the range of names as: LIST
2) Use this formula to return the most appeared name:
=INDEX(LIST,MODE(MATCH(LIST,LIST,)))
Micky
 
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=INDEX(A1:A1000,MODE(IF(A1:A1000<>"",MATCH(A1:A1000,A1:A1000,0))))
 
Back
Top