I'd give the same answer as I posted earlier,
http://www.mrexcel.com/board2/viewtopic.php?t=23178
but there is more to it...
What follows also invokes the solution to the so-called Top N class of problems.
Ordinarily, the formula in G1...
=INDEX(A2:A8,MATCH(MAX(COUNTIF(A2:A8,A2:A8)),COUNTIF(A2:A8,A2:A8),0))
which must be confirmed with control+shift+enter instead of just enter, is the answer. G2 houses the same using morefunc...
=INDEX(A2:A8,MATCH(MAX(SETV(COUNTIF(A2:A8,A2:A8))),GETV(),0))
However, a data set can have two or more MODE values...
The following formula set captures multiple values...
B2:
=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",COUNTIF($A$2:$A$8,A2))
C2:
=IF(N(B2),RANK(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)-1,"")
E1 houses 1 (meaning: Top N with N = 1)
E2:
=MAX(IF(INDEX(B2:B8,MATCH(E1,C2:C8,0))=B2:B8,C2:C8))-E1
which must be confirmed with control+shift+enter instead of just with enter.
E3:
=IF(ROW()-ROW($E$3)+1<=$E$1+$E$2,INDEX($A$2:$A$8,MATCH(ROW()-ROW($E$3)+1,$C$2:$C$8,0)),"")
Formulas
