Calculating 95th percentile on grouped data

dimsum1

New Member
Joined
Aug 30, 2014
Messages
6
Does anyone have a function that will calculate the 95th percentile (or any percentile for that matter) on grouped data? I see that there are numerous methods to calculate the percentiles on the entire table but I haven't found any that work correctly on grouped data. - Thanks
table1
LocationTypesampnameanaresult
Loc1WetSample1ana112
Loc1WetSample1ana23
Loc1WetSample1ana315
Loc1Wetsample2ana144
Loc1Wetsample2ana223
Loc1Wetsample2ana325
Loc1Wetsample2ana412
Loc1Drysample3ana144
Loc1Drysample3ana255
Loc1Drysample3ana376
Loc1Drysample4ana18
Loc1Drysample4ana257
Loc1Drysample4ana346
Loc1Drysample4ana499
Loc2Wetsample5ana1124
Loc2Wetsample5ana266
Loc2Wetsample5ana36
Loc2Wetsample6ana1789
Loc2Wetsample6ana288
Loc2Wetsample6ana3767
Loc2Wetsample6ana4565

<tbody>
</tbody>
grouping on location, type and ana the return would look as follows:
LocationTypeana95th
Loc1Wetana142.4
Loc1Wetana222
Loc1Wetana324.5
Loc1Wetana412
Loc1Dryana142.2
Loc1Dryana256.9
Loc1Dryana374.5
Loc1Dryana499
Loc2Wetana1755.75
Loc2Wetana286.9
Loc2Wetana3728.95
Loc2Wetana4565

<tbody>
</tbody>
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,988
Confused, you want the 95% of each LOCATION? Wouldnt that be
LOC1 = 13 records
LOC2 = 6 records
?
It doesnt seem to be RESULT value.
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Location</td><td style=";">Type</td><td style=";">ana</td><td style=";">sampname</td><td style=";">result</td><td style=";">95th</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana1</td><td style=";">Sample1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">42.4</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana2</td><td style=";">Sample1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana3</td><td style=";">Sample1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">24.5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana1</td><td style=";">sample2</td><td style="text-align: right;;">44</td><td style="text-align: right;;">42.4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana2</td><td style=";">sample2</td><td style="text-align: right;;">23</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana3</td><td style=";">sample2</td><td style="text-align: right;;">25</td><td style="text-align: right;;">24.5</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana4</td><td style=";">sample2</td><td style="text-align: right;;">12</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana1</td><td style=";">sample3</td><td style="text-align: right;;">44</td><td style="text-align: right;;">42.2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana2</td><td style=";">sample3</td><td style="text-align: right;;">55</td><td style="text-align: right;;">56.9</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana3</td><td style=";">sample3</td><td style="text-align: right;;">76</td><td style="text-align: right;;">74.5</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana1</td><td style=";">sample4</td><td style="text-align: right;;">8</td><td style="text-align: right;;">42.2</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana2</td><td style=";">sample4</td><td style="text-align: right;;">57</td><td style="text-align: right;;">56.9</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana3</td><td style=";">sample4</td><td style="text-align: right;;">46</td><td style="text-align: right;;">74.5</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana4</td><td style=";">sample4</td><td style="text-align: right;;">99</td><td style="text-align: right;;">99</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana1</td><td style=";">sample5</td><td style="text-align: right;;">124</td><td style="text-align: right;;">755.75</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana2</td><td style=";">sample5</td><td style="text-align: right;;">66</td><td style="text-align: right;;">86.9</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana3</td><td style=";">sample5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">728.95</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana1</td><td style=";">sample6</td><td style="text-align: right;;">789</td><td style="text-align: right;;">755.75</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana2</td><td style=";">sample6</td><td style="text-align: right;;">88</td><td style="text-align: right;;">86.9</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana3</td><td style=";">sample6</td><td style="text-align: right;;">767</td><td style="text-align: right;;">728.95</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana4</td><td style=";">sample6</td><td style="text-align: right;;">565</td><td style="text-align: right;;">565</td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1 (3)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">{=PERCENTILE(<font color="Blue">(<font color="Red">IF(<font color="Green">$A$2:$A$22=A2,IF(<font color="Purple">$B$2:$B$22=B2,IF(<font color="Teal">$C$2:$C$22=C2,$E$2:$E$22</font>)</font>)</font>)</font>),0.95</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />


Then apply the advanced filter, unique records only, on just the first three columns (I moved one over because it seems they have to be together):

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Location</td><td style=";">Type</td><td style=";">ana</td><td style=";">sampname</td><td style=";">result</td><td style=";">95th</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana1</td><td style=";">Sample1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">42.4</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana2</td><td style=";">Sample1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana3</td><td style=";">Sample1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">24.5</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Loc1</td><td style=";">Wet</td><td style=";">ana4</td><td style=";">sample2</td><td style="text-align: right;;">12</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana1</td><td style=";">sample3</td><td style="text-align: right;;">44</td><td style="text-align: right;;">42.2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana2</td><td style=";">sample3</td><td style="text-align: right;;">55</td><td style="text-align: right;;">56.9</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana3</td><td style=";">sample3</td><td style="text-align: right;;">76</td><td style="text-align: right;;">74.5</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Loc1</td><td style=";">Dry</td><td style=";">ana4</td><td style=";">sample4</td><td style="text-align: right;;">99</td><td style="text-align: right;;">99</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana1</td><td style=";">sample5</td><td style="text-align: right;;">124</td><td style="text-align: right;;">755.75</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana2</td><td style=";">sample5</td><td style="text-align: right;;">66</td><td style="text-align: right;;">86.9</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana3</td><td style=";">sample5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">728.95</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Loc2</td><td style=";">Wet</td><td style=";">ana4</td><td style=";">sample6</td><td style="text-align: right;;">565</td><td style="text-align: right;;">565</td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1 (3)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">{=PERCENTILE(<font color="Blue">(<font color="Red">IF(<font color="Green">$A$2:$A$22=A2,IF(<font color="Purple">$B$2:$B$22=B2,IF(<font color="Teal">$C$2:$C$22=C2,$E$2:$E$22</font>)</font>)</font>)</font>),0.95</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />


and hide the result/sampname column....
 
Last edited:

dimsum1

New Member
Joined
Aug 30, 2014
Messages
6

ADVERTISEMENT

Oh wait, you want Access, hang on...............

Hi, hey this is a fantastic alternative than trying to get access to hammer this out. Many thanks!!!!! I think it may be easier to pull the data from access, work it out this way, then just suck the stats back into access as a new table.
Thanks again. Of course if you have a access module that does it that would be great as well but this is still a HUGE help. Thanks again.
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
Of course if you have a access module that does it that would be great as well

I was a little hasty in implying so, it appeared much easier before some research:

From Percentile - Wikipedia, the free encyclopedia (scroll down to Excel's Algorithm, Access as you said has no percentile function):

the formula is in 2 parts:

1. (number of terms - 1)*percentile = integer part + decimal part

2. (integer part + 1)th term + decimal part*((integer part + 2)th term - (integer part + 1)th term)

So for your first set, {12,44}:

1. (2-1)*.95 = 0 + .95

2. 12 + .95(44 - 12) = 12 + 30.4 = 42.4

Since Index, like percentile, is another Excel function that Access doesn't have, you'll probably need VBA to determine the nth position within the group arrays (I don't know if SQL alone can do this, at least in Access JET).

Short answer: I'm glad you didn't say this has to be done in Access
 

dimsum1

New Member
Joined
Aug 30, 2014
Messages
6
I was a little hasty in implying so, it appeared much easier before some research:

From Percentile - Wikipedia, the free encyclopedia (scroll down to Excel's Algorithm, Access as you said has no percentile function):

the formula is in 2 parts:

1. (number of terms - 1)*percentile = integer part + decimal part

2. (integer part + 1)th term + decimal part*((integer part + 2)th term - (integer part + 1)th term)

So for your first set, {12,44}:

1. (2-1)*.95 = 0 + .95

2. 12 + .95(44 - 12) = 12 + 30.4 = 42.4

Since Index, like percentile, is another Excel function that Access doesn't have, you'll probably need VBA to determine the nth position within the group arrays (I don't know if SQL alone can do this, at least in Access JET).

Short answer: I'm glad you didn't say this has to be done in Access

Yes I went through this math last night and quickly realized why 99% of the access functions posted for this produce erroneous data. I think using access to do this is like using a rock to pound in a nail when you have a nice hammer sitting right next to it.
I found one add in that worked, but it was a trial version and I could envision it churning away on a large data set for hours when I need the data in 10 minutes.
Again, your solution enabled us to leave on time and get some sleep tonight rather than hand grouping thousands of records and performing stats on the individual groups. I cant thank you enough! It validated at 100% too! Pats sheetspread on back and says thanks!
 

dimsum1

New Member
Joined
Aug 30, 2014
Messages
6
Confused, you want the 95% of each LOCATION? Wouldnt that be
LOC1 = 13 records
LOC2 = 6 records
?
It doesnt seem to be RESULT value.

No that is where the grouping becomes necessary to doing the statistics properly in one shot. To calc the stats correctly these three groups would have four different stat calcs One for each ana.
Loc 1 wet = 4 ana
Loc 1 Dry = 4 ana
Loc 2 Wet = 4 ana

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,132,686
Messages
5,654,749
Members
418,149
Latest member
amamiche67

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
Top