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

 

[alizok]

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

I'm getting an error message if i try to load excel and i can't seem to download xl2BB

 

[awoohaw]

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

Try putting this in cell B3 in my example above:

=AVERAGE(IF(ROWS($A$3:A3)>COUNTA($A:$A)*(10/100),"", AGGREGATE(14,4,$A:$A, COUNTA($A$3:$A3))))

 

[alizok]

no it still doesn't seem to be doing what i need. Please see the screen shot attached. The top 10% of 53 employees equal to 5.3 in which case i take the average of the top 5 employees to figure out the earnings for the top 10% of employees. For the next 25% i look at the top 13 employees (53*25%) and so on. The same is true for the bottom but instead of looking for the top i'm looking at the bottom 5EEs .

 

[Fluff]

As you cannot use the XL2BB add-in, can you just copy/paste the data.

 

[alizok]

here is the data

 

[Fluff]

That is another image & I see no point in retyping what you have already got. So once again please post your data not an image.

 

[alizok]

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

would you please let me know how to do it?

 

[Fluff]

Just copy/paste the data to the board.

 

[alizok]

1	A	B	C	D	E	F	G
2
3			count	Results		count	Results
4	Top 10		5​	$26,800​		5​	$236,320​
5	Top 25%		13​	$36,690​		13​	$190,342​
6	Avg			$30,054​			$111,081​
7	Bottom 25%		13​	$20,667​		13​	$88,258​
8	Bottom 10%		5​	$13,375​		5​	$85,365​
9
10		Total count	53​			51​
11		Jan Attn%	Jan Attn% Rank	Jan Earning$	Feb-Oct Attn%	Feb-Oct Attn% Rank	Feb-Oct Earning$
12		97.2%	42	$15,475	30810.0%	1	$136,775
13		244.4%	5	$67,500	9220.0%	2	$282,500
14		191.5%	13	$78,300	7333.3%	3	$296,025
15		166.7%	14	$56,875	7300.0%	4	$225,625
16		215.6%	9	$78,850	6720.0%	5	$240,675
17		151.1%	19	$47,550	6600.0%	6	$225,450
18		195.6%	11	$69,850	6340.0%	7	$252,250
19		115.6%	30	$43,225	5400.0%	8	$185,525
20		137.8%	22	$44,425	4680.0%	9	$140,525
21		148.9%	20	$45,200	4620.0%	10	$145,775
22		88.9%	44	$14,950	4540.0%	11	$118,975
23		153.3%	17	$43,175	4280.0%	12	$92,550
24		84.4%	47	$17,025	4260.0%	13	$131,800
25		130.5%	23	$32,575	4133.3%	14	$140,700
26		86.7%	45	$27,775	4020.0%	15	$154,850
27		104.4%	33	$22,925	3940.0%	16	$98,725
28		117.8%	29	$24,175	3540.0%	17	$78,050
29		97.8%	41	$43,300	3520.0%	18	$127,500
30		84.4%	47	$12,150	3480.0%	19	$80,750
31		102.2%	34	$27,350	3220.0%	20	$61,725
32		128.9%	24	$46,975	3120.0%	21	$81,825
33		84.4%	47	$23,325	3100.0%	22	$68,175
34		111.1%	32	$25,425	2920.0%	23	$93,950
35		102.2%	34	$39,375	2920.0%	23	$62,650
36		113.6%	31	$30,275	2883.3%	25	$75,450
37		194.4%	12	$22,225	2860.0%	26	$49,250
38		83.3%	51	$11,750	2780.0%	27	$59,000
39		81.0%	52	$2,650	2766.7%	28	$47,775
40		91.5%	43	$54,525	2700.0%	29	$97,725
41		166.7%	14	$23,650	2660.0%	30	$83,975
42		151.8%	18	$49,800	2383.3%	31	$60,075
43		118.6%	28	$35,225	2283.3%	32	$55,725
44		84.4%	47	$16,275	2220.0%	33	$37,900
45		100.0%	36	$5,050	2180.0%	34	$36,775
46		86.7%	45	$12,475	2180.0%	34	$46,950
47		280.0%	1	$25,900	2140.0%	36	$154,300
48		100.0%	36	$29,650	1240.0%	37	$110,675
49		260.0%	2	$13,200	1200.0%	38	$78,875
50		200.0%	10	$12,425	1160.0%	39	$108,650
51		220.0%	7	$33,550	980.0%	40	$107,300
52		100.0%	36	$20,500	880.0%	41	$89,050
53		100.0%	36	$17,100	760.0%	42	$87,075
54		260.0%	2	$14,650	560.0%	43	$56,950
55		260.0%	2	$12,750	520.0%	44	$81,500
56		220.0%	7	$8,350	520.0%	44	$87,125
57		160.0%	16	$26,325	520.0%	44	$102,875
58		120.0%	25	$26,075	440.0%	47	$89,875
59		80.0%	53	$17,000	400.0%	48	$87,050
60		140.0%	21	$19,850	380.0%	49	$79,500
61		100.0%	36	$27,925	320.0%	50	$99,950
62		120.0%	25	$10,950	200.0%	51	$70,450
63		231.1%	6	$39,425	-	n/a	n/a
64		120.0%	25	$25,575	-	n/a	n/a

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D
1
2
3			count	Results
4	Top 10		5	£26,800
5	Top 25%		13	£36,690
6	Avg			£30,054
7	Bottom 25%		13	£20,667
8	Bottom 10%		5	£14,200
9
10		Total count	53
11		Jan Attn%	Jan Attn% Rank	Jan Earning
12		97.20%	42	15,475
13		244.40%	5	67,500
14		191.50%	13	78,300
15		166.70%	14	56,875
16		215.60%	9	78,850
17		151.10%	19	47,550
18		195.60%	11	69,850
19		115.60%	30	43,225
20		137.80%	22	44,425
21		148.90%	20	45,200
22		88.90%	44	14,950
23		153.30%	17	43,175
24		84.40%	47	17,025
25		130.50%	23	32,575
26		86.70%	45	27,775
27		104.40%	33	22,925
28		117.80%	29	24,175
29		97.80%	41	43,300
30		84.40%	47	12,150
31		102.20%	34	27,350
32		128.90%	24	46,975
33		84.40%	47	23,325
34		111.10%	32	25,425
35		102.20%	34	39,375
36		113.60%	31	30,275
37		194.40%	12	22,225
38		83.30%	51	11,750
39		81.00%	52	2,650
40		91.50%	43	54,525
41		166.70%	14	23,650
42		151.80%	18	49,800
43		118.60%	28	35,225
44		84.40%	47	16,275
45		100.00%	36	5,050
46		86.70%	45	12,475
47		280.00%	1	25,900
48		100.00%	36	29,650
49		260.00%	2	13,200
50		200.00%	10	12,425
51		220.00%	7	33,550
52		100.00%	36	20,500
53		100.00%	36	17,100
54		260.00%	2	14,650
55		260.00%	2	12,750
56		220.00%	7	8,350
57		160.00%	16	26,325
58		120.00%	25	26,075
59		80.00%	53	17,000
60		140.00%	21	19,850
61		100.00%	36	27,925
62		120.00%	25	10,950
63		231.10%	6	39,425
64		120.00%	25	25,575
Main

Cell Formulas
Range	Formula
D4:D8	D4	=LET(data,SORT(C12:D64),r,ROWS(data),VSTACK(AVERAGE(TAKE(data,r*0.1,-1)),AVERAGE(TAKE(data,r*0.25,-1)),AVERAGE(TAKE(data,,-1)),AVERAGE(TAKE(data,-r*0.25,-1)),AVERAGE(TAKE(data,-r*0.1,-1))))
Dynamic array formulas.