Ranking

[Guest]

How do I obtain a ranking in excel from a lits of numbers based on the
frequency with which the numbers show up?
ie) list of numbers: 6,5,2,2,8,4,2,0,3,3,7,3,8,3,8,3
Rank: 1. #3
2. #2 &#8
3. #0,4,5,6,7

 

[Guest]

Look at the FREQUENCY function. You have to specify the data array and bins
array.

Dave

 

[Domenic]

Assuming that A2:A17 contains the data, try the following...

B2, copied down:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),COUNTIF($A$2:$A$17,A2),"")

C2, copied down:

=IF(N(B2),SUM(IF($B$2:$B$17<>"",IF(B2<$B$2:$B$17,1/COUNTIF($B$2:$B$17,$B$
2:$B$17))))+1,"")

....confirmed with CONTROL+SHIFT+ENTER

Let E2:E4 contain 1, 2, and 3

F2, copied across and down:

=IF(COLUMNS($F2:F2)<=COUNTIF($C$2:$C$17,$E2),SMALL(IF($C$2:$C$17=$E2,$A$2
:$A$17),COLUMNS($F2:F2)),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!