Dynamic Top/bottom calculation

[alizok]

I need the assistance of this group in developing a formula that will dynamically modify dependent on the number of records in the database. I need to figure out the top 10%, top 25%, top 30%, bottom 10%, bottom 25%, and so on. Is there a formula that can automatically calculate this information? Typically, I rank the data, then sort it from smallest to largest, and then determine the average of a certain data set manually depending on the percentage. see attached for more details.

Thank you in advance for all your help

 

[awoohaw]

Try this:
(Cannot have any extraneoius data in column A.)

mr excel questions 12.xlsm
	A	B	C	D	E	F	G
1		Top 10%	Top 25%	Top 30%	Bottom 30%	Bottom 25%	Bottom 10%
2		10	25	30	10	25	30
3	54	291	291	291	3	3	3
4	208	291	291	291	22	22	22
5	223	287	287	287	36	36	36
6	287	280	280	280	49	49	49
7	134	267	267	267	54	54	54
8	291		266	266		58	58
9	3		266	266		69	69
10	250		258	258		77	77
11	209		257	257		95	95
12	258		251	251		98	98
13	144		250	250		101	101
14	257		245	245		103	103
15	291			240			104
16	206			230			107
17	36			228			120
18	266
19	205
20	199
21	228
22	120
23	104
24	77
25	245
26	49
27	210
28	22
29	172
30	107
31	192
32	135
33	208
34	69
35	230
36	181
37	95
38	58
39	227
40	280
41	240
42	103
43	139
44	98
45	101
46	145
47	267
48	155
49	266
50	204
51	201
52	251
Sheet20

Cell Formulas
Range	Formula
B3:D52	B3	=IF(ROWS($A$3:A3)>COUNTA($A:$A)*(B$2/100),"", AGGREGATE(14,4,$A:$A, COUNTA($A$3:$A3)))
E3:G52	E3	=IF(ROWS($A$3:D3)>COUNTA($A:$A)*(E$2/100),"", AGGREGATE(15,4,$A:$A, COUNTA($A$3:$A3)))

 

[awoohaw]

Try this:
(Cannot have any extraneoius data in column A.)

mr excel questions 12.xlsm
	A	B	C	D	E	F	G
1		Top 10%	Top 25%	Top 30%	Bottom 30%	Bottom 25%	Bottom 10%
2		10	25	30	10	25	30
3	54	291	291	291	3	3	3
4	208	291	291	291	22	22	22
5	223	287	287	287	36	36	36
6	287	280	280	280	49	49	49
7	134	267	267	267	54	54	54
8	291		266	266		58	58
9	3		266	266		69	69
10	250		258	258		77	77
11	209		257	257		95	95
12	258		251	251		98	98
13	144		250	250		101	101
14	257		245	245		103	103
15	291			240			104
16	206			230			107
17	36			228			120
18	266
19	205
20	199
21	228
22	120
23	104
24	77
25	245
26	49
27	210
28	22
29	172
30	107
31	192
32	135
33	208
34	69
35	230
36	181
37	95
38	58
39	227
40	280
41	240
42	103
43	139
44	98
45	101
46	145
47	267
48	155
49	266
50	204
51	201
52	251
Sheet20

Cell Formulas
Range	Formula
B3:D52	B3	=IF(ROWS($A$3:A3)>COUNTA($A:$A)*(B$2/100),"", AGGREGATE(14,4,$A:$A, COUNTA($A$3:$A3)))
E3:G52	E3	=IF(ROWS($A$3:D3)>COUNTA($A:$A)*(E$2/100),"", AGGREGATE(15,4,$A:$A, COUNTA($A$3:$A3)))

(the above will not list all ties of the last of the nths.)

 

[awoohaw]

also for the above you need excel 2010 or greater, maybe 2013. Because it uses the aggregate functions.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alizok]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have updated my record Microsoft 365 - thank you.

 

[alizok]

(the above will not list all ties of the last of the nths.)

thank you for the formula but I'm not sure if it's doing what I'm looking for. I just simply need to enter the count of records in the row and then the excel knows to calculate the average of the top or bottom records. In your example, if I'm looking for the top 10% the result should be 256.29

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[alizok]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Fluff]

That is an image, please post the actual data as requested.