Am looking for the function/macro to return the most frequently ocurring
numbers within in large (300+cell) range in descending order to the 10th
place. I am new to excel and can't figure this one out! Any help is
appreciated!

Thanks, Travis B.

P.S. I'm using Excel 2003 in Office.

Assuming that A2:A300 contains your numbers , the following is a formula
system that will list the Top 10 most frequently occuring numbers,
including any ties for 10th place...

B2, copied down:

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

C2, copied down:

=IF(N(B2),RANK(B2,$B$2:$B$300)+COUNTIF($B$2:B2,B2)-1,"")

D1: enter 10, indicating that you want a Top 10 list

E1:

=MAX(IF(B2:B300=INDEX(B2:B300,MATCH(D1,C2:C300,0)) ,C2:C300))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER

F2, copied down:

=IF(ROW()-ROW(F$2)+1<=$D$1+$E$1,INDEX($A$2:$A$300,MATCH(ROW( )-ROW(F$2)+1,
$C$2:$C$300,0)),"")

If, for example, at some point you want a Top 5 list, change the 10 in
D1 to 5.

Hope this helps!

In article ,
"trav" wrote:

Am looking for the function/macro to return the most frequently ocurring
numbers within in large (300+cell) range in descending order to the 10th
place. I am new to excel and can't figure this one out! Any help is
appreciated!

Thanks, Travis B.

Thanks Domenic,

I think that will work with a few adjustments.... The range is actually
B2:G51, but I think with just a slight mod here and there I can make what you
gave me work.
Thanks again!
Travis B.

"Domenic" wrote:

Assuming that A2:A300 contains your numbers , the following is a formula
system that will list the Top 10 most frequently occuring numbers,
including any ties for 10th place...

B2, copied down:

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

C2, copied down:

=IF(N(B2),RANK(B2,$B$2:$B$300)+COUNTIF($B$2:B2,B2)-1,"")

D1: enter 10, indicating that you want a Top 10 list

E1:

=MAX(IF(B2:B300=INDEX(B2:B300,MATCH(D1,C2:C300,0)) ,C2:C300))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER

F2, copied down:

=IF(ROW()-ROW(F$2)+1<=$D$1+$E$1,INDEX($A$2:$A$300,MATCH(ROW( )-ROW(F$2)+1,
$C$2:$C$300,0)),"")

If, for example, at some point you want a Top 5 list, change the 10 in
D1 to 5.

Hope this helps!

In article ,
"trav" wrote:

Am looking for the function/macro to return the most frequently ocurring
numbers within in large (300+cell) range in descending order to the 10th
place. I am new to excel and can't figure this one out! Any help is
appreciated!

Thanks, Travis B.