Median & Percentile for the range of various lengths

T

trader1011

New Member
Joined
Sep 24, 2013
Messages
7
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

[TABLE="class: outer_border, width: 200"]
<tbody>[TR]
[TD]Batch #[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Table 2: Result

[TABLE="class: outer_border, width: 200"]
<tbody>[TR]
[TD]Set (Batch no.)[/TD]
[TD]Median[/TD]
[TD]75th Percentile[/TD]
[/TR]
[TR]
[TD]1-3[/TD]
[TD]5[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]2-4[/TD]
[TD]4[/TD]
[TD]10.25[/TD]
[/TR]
[TR]
[TD]3-5[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4-6[/TD]
[TD]3.5[/TD]
[TD]9.5[/TD]
[/TR]
[TR]
[TD]5-7[/TD]
[TD]6.5[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Perhaps:

ABCDEFG
ValueSet: Low BatchSet: High BatchMedian75th Percentile
5
1

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Batch #[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10.25[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]9.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=D2+2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
Glad to help! :cool:

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Formula to include quantities in percentile calculation
Replies
5
Views
137
Fluff
Fluff
smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
101
hagia_sofia
hagia_sofia
M
  • Question Question
Formula to Split Start/End Times into Hour Brackets
Replies
4
Views
249
felixstraube
F
G
  • Solved
Typing out Excel formula in Excel Macro
Replies
1
Views
47
Macropod
Macropod
W
  • Question Question
Dynamic formula to show the day and the number of cycles that can be ran that day
Replies
6
Views
111
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top