picking the 7 highest numbers

[Jaffa_ca]

i need some help with a formula,
what i want do to is to have excel go through the document and count how many times each number (from 1 - 30) appears and then sort them according to how many times the number actullay does appear. follow?
ex. 1 appears 5 times
2 appears 3 times
3 appears 2 times
4 appears 7 times
5 appears 9 times
so i want it so tell me how times each number appears and sort them from highest to lowest

 

[plettieri]

Hi Jaffa_ca

You might try here as a start...

http://www.mrexcel.com/board2/viewtopic.php?t=78995&highlight=largest++numbers#javascript:void(0);


plettieri

 

[Aladin Akyurek]

i need some help with a formula,
what i want do to is to have excel go through the document and count how many times each number (from 1 - 30) appears and then sort them according to how many times the number actullay does appear. follow?
ex. 1 appears 5 times
2 appears 3 times
3 appears 2 times
4 appears 7 times
5 appears 9 times
so i want it so tell me how times each number appears and sort them from highest to lowest

Book8
	A	B	C	D	E
1	Num	Freq		8
2	3	1		Num	Freq
3	1	2		8	5
4	2	4		2	4
5	2			5	3
6	8	5		1	2
7	8			6	2
8	7	1		3	1
9	4	1		7	1
10	8			4	1
11	2
12	6	2
13	2
14	6
15	8
16	5	3
17	8
18	1
19	5
20	5
Sheet1

B2, copied down:

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

D1:

=COUNT(B2:B20)

D3:

=IF(N(E3),INDEX(A$2:A$20,SMALL(IF(B$2:B$20=E3,ROW(B$2:B$20)-ROW(B$2)+1),COUNTIF(E$3:E3,E3))),"")

which is confirmed with control+shift+enter (not with enter) and copied down.

E3:

=IF(ROWS($E$3:E3)<=$D$1,LARGE($B$2:$B$20,ROWS($E$3:E3)),"")

 

[tusharm]

Let XL do the work for you. Create a PivotTable and use it's option to 'Show Top N' with N set to 7.

i need some help with a formula,
what i want do to is to have excel go through the document and count how many times each number (from 1 - 30) appears and then sort them according to how many times the number actullay does appear. follow?
ex. 1 appears 5 times
2 appears 3 times
3 appears 2 times
4 appears 7 times
5 appears 9 times
so i want it so tell me how times each number appears and sort them from highest to lowest

 

[Aladin Akyurek]

More in line with your topic title, a more recent version for Top N lists plettieri quoted:

http://www.mrexcel.com/board2/viewtopic.php?t=234201

 

[Jaffa_ca]

Great thks so much for the help, i used the pivot table, and it works good
i just can't figure out how to make it only choose the 7 highest numbers but i think i'll might be able to figure it out in the morn

 

[Aladin Akyurek]

Great thks so much for the help, i used the pivot table, and it works good
i just can't figure out how to make it only choose the 7 highest numbers but i think i'll might be able to figure it out in the morn

Just change the default value of 10 to 7.

 

[Jaffa_ca]

i don't see any option for the pivot table called default value, btw this is excel 2003 if it makes any difference

 

[Jaffa_ca]

never mind i'm total stupid i got it, thx for all you help