Median & Percentile for the range of various lengths

[trader1011]

Hi,

I am trying to calculate the Median & 75th Percentile for the range in array where the range differs in the length. For example, my data looks like the one given below (my data-set is almost 100,000 rows). Now I am trying to find the median and 75th percentile values of a combined set of 3 batches where every next batch will drop the first member and and the next member (Set 1 - Batch no. 1, 2,3 & Set 2 - Batch no. 2, 3, 4 etc.). I have provided the result in the table no. 2.

As one can see from the data-set the length of range (no. of data points for each batch) is varying.

Any help will be appreciated.

Table 1: Data-set

Batch #	Value
1	1
1	12
1	10
1	5
2	3
2	23
2	6
3	2
3	5
4	78
4	2
4	3
4	1
4	5
5	4
5	9
6	1
6	3
6	11
7	15

<tbody>
</tbody>

Table 2: Result

Set (Batch no.)	Median	75th Percentile
1-3	5	11
2-4	4	10.25
3-5	4	7
4-6	3.5	9.5
5-7	6.5	12

<tbody>
</tbody>

 

[Eric W]

Perhaps:

	A	B	C	D	E	F	G
1	Batch #	Value		Set: Low Batch	Set: High Batch	Median	75th Percentile
2	1	1		1	3	5	11
3	1	12		2	4	4	10.25
4	1	10		3	5	4	7
5	1	5		4	6	3.5	9.5
6	2	3		5	7	6.5	12
7	2	23				#N/A	#N/A
8	2	6
9	3	2
10	3	5
11	4	78
12	4	2
13	4	3
14	4	1
15	4	5
16	5	4
17	5	9
18	6	1
19	6	3
20	6	11
21	7	15

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E2 =D2+2 F2 =MEDIAN(INDEX(B:B,MATCH(D2,A:A,0)):INDEX(B:B,LOOKUP(2,1/(A$2:A$21=E2),ROW(A$2:A$21)))) G2 =PERCENTILE.EXC(INDEX(B:B,MATCH(D2,A:A,0)):INDEX(B:B,LOOKUP(2,1/(A$2:A$21=E2),ROW(A$2:A$21))),0.75) <tbody> </tbody>

<tbody>
</tbody>

 

[trader1011]

Amazing Eric. Thanks for the help. It worked quite well. I am going to try to understand the logic you have used in the formula though.

 

[Eric W]

Glad to help!

To help you out, this part of the formula:

MATCH(D2,A:A,0)

finds the first instance of D2 in column A, and this part:

LOOKUP(2,1/(A$2:A$21=E2),ROW(A$2:A$21))

finds the last instance of E2 in A2:A21.