Calculating percentiles on subset of data

[ExcelQ12]

Hi,

I am hoping someone here will have a creative solution to my problem. I am novice at vba and dynamic ranges always mess me up. I have a set of data, lets say 1000 rows. I want to do percentiles on this data. However it gets a bit more complicated. Of the 1000 rows I have the data broken out in chunks of varying length. For example chunk 1 could be row 1 to 100, chunk 2 is row 101 to 144. Chunk 3 is row 145 to 207. Etc. I want to percentile each of these chunks.

I know the formula to calculate the percentile is =percentile.exc(range, 0.1), then =percentile.exc(range, 0.2), etc until 0.9.

For example on the below data, I want to percentile on the $ column but broken up by set. So my first set of percentiles would be =percentile.exc(C$2$:C$5$, 0.1) and my second set of percentiles would be =percentile.exc(C$6$:C$10$, 0.1). Is there a vba way to determine the data ranges?

Item	Set	$
1	1	100
2	1	150
3	1	250
4	1	300
5	2	4
6	2	75
7	2	84
8	2	200
9	2	500

 

[Jeffrey Mahoney]

I think you should create dynamic named ranges.

 

[Jeffrey Mahoney]

I changed my mind. Please try these formulas:

The percentile formula uses values from the table to reference the correct part of the table. You will definitely have to have your original table sorted by SET or it won't work. The formula starting in cell G2 uses a dynamic range based on the values in row 1 above.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Item	Set	$			Rank	1	2	3	4	5
2	1	1	100			0.1	115	32.4	#N/A	6.2	#N/A
3	2	1	150			0.2	130	60.8	#N/A	7.4	#N/A
4	3	1	250			0.3	145	76.8	#N/A	8.6	#N/A
5	4	1	300			0.4	170	80.4	#N/A	9.8	#N/A
6	5	2	4			0.5	200	84	#N/A	11	#N/A
7	6	2	75			0.6	230	130.4	#N/A	15.4	#N/A
8	7	2	84			0.7	255	176.8	#N/A	19.8	#N/A
9	8	2	200			0.8	270	260	#N/A	24.2	#N/A
10	9	2	500			0.9	285	380	#N/A	28.6	#N/A
11	10	4	5			1	300	500	#N/A	33	#N/A
12	11	4	11
13	12	4	33
Sheet3

Cell Formulas
Range	Formula
G2:K11	G2	=PERCENTILE(OFFSET(Sheet3!$C$1,MATCH(G$1,Sheet3!$B:$B,0)-1,0,MATCH(G$1,Sheet3!$B:$B)-MATCH(G$1,Sheet3!$B:$B,0)+1,1),$F2)

 

[ExcelQ12]

Hi Jeff,

Thanks for getting back to me. I ended up going another route as I did not see this reply in time. Thank you for your effort though and I have saved this thread, as I imagine I will come to this issue again.