# Calculating 95th percentile on grouped data

#### dimsum1

##### New Member
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 Location Type sampname ana result Loc1 Wet Sample1 ana1 12 Loc1 Wet Sample1 ana2 3 Loc1 Wet Sample1 ana3 15 Loc1 Wet sample2 ana1 44 Loc1 Wet sample2 ana2 23 Loc1 Wet sample2 ana3 25 Loc1 Wet sample2 ana4 12 Loc1 Dry sample3 ana1 44 Loc1 Dry sample3 ana2 55 Loc1 Dry sample3 ana3 76 Loc1 Dry sample4 ana1 8 Loc1 Dry sample4 ana2 57 Loc1 Dry sample4 ana3 46 Loc1 Dry sample4 ana4 99 Loc2 Wet sample5 ana1 124 Loc2 Wet sample5 ana2 66 Loc2 Wet sample5 ana3 6 Loc2 Wet sample6 ana1 789 Loc2 Wet sample6 ana2 88 Loc2 Wet sample6 ana3 767 Loc2 Wet sample6 ana4 565

<tbody>
</tbody>
grouping on location, type and ana the return would look as follows:
 Location Type ana 95th Loc1 Wet ana1 42.4 Loc1 Wet ana2 22 Loc1 Wet ana3 24.5 Loc1 Wet ana4 12 Loc1 Dry ana1 42.2 Loc1 Dry ana2 56.9 Loc1 Dry ana3 74.5 Loc1 Dry ana4 99 Loc2 Wet ana1 755.75 Loc2 Wet ana2 86.9 Loc2 Wet ana3 728.95 Loc2 Wet ana4 565

<tbody>
</tbody>

Last edited:

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Confused, you want the 95% of each LOCATION? Wouldnt that be
LOC1 = 13 records
LOC2 = 6 records
?
It doesnt seem to be RESULT value.

Excel 2010
ABCDEF
1LocationTypeanasampnameresult95th
2Loc1Wetana1Sample11242.4
3Loc1Wetana2Sample1322
4Loc1Wetana3Sample11524.5
5Loc1Wetana1sample24442.4
6Loc1Wetana2sample22322
7Loc1Wetana3sample22524.5
8Loc1Wetana4sample21212
9Loc1Dryana1sample34442.2
10Loc1Dryana2sample35556.9
11Loc1Dryana3sample37674.5
12Loc1Dryana1sample4842.2
13Loc1Dryana2sample45756.9
14Loc1Dryana3sample44674.5
15Loc1Dryana4sample49999
16Loc2Wetana1sample5124755.75
17Loc2Wetana2sample56686.9
18Loc2Wetana3sample56728.95
19Loc2Wetana1sample6789755.75
20Loc2Wetana2sample68886.9
21Loc2Wetana3sample6767728.95
22Loc2Wetana4sample6565565
Sheet1 (3)
Cell Formulas
RangeFormula
F2{=PERCENTILE((IF(\$A\$2:\$A\$22=A2,IF(\$B\$2:\$B\$22=B2,IF(\$C\$2:\$C\$22=C2,\$E\$2:\$E\$22)))),0.95)}
Press CTRL+SHIFT+ENTER to enter array formulas.

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):

Excel 2010
ABCDEF
1LocationTypeanasampnameresult95th
2Loc1Wetana1Sample11242.4
3Loc1Wetana2Sample1322
4Loc1Wetana3Sample11524.5
8Loc1Wetana4sample21212
9Loc1Dryana1sample34442.2
10Loc1Dryana2sample35556.9
11Loc1Dryana3sample37674.5
15Loc1Dryana4sample49999
16Loc2Wetana1sample5124755.75
17Loc2Wetana2sample56686.9
18Loc2Wetana3sample56728.95
22Loc2Wetana4sample6565565
Sheet1 (3)
Cell Formulas
RangeFormula
F2{=PERCENTILE((IF(\$A\$2:\$A\$22=A2,IF(\$B\$2:\$B\$22=B2,IF(\$C\$2:\$C\$22=C2,\$E\$2:\$E\$22)))),0.95)}
Press CTRL+SHIFT+ENTER to enter array formulas.

and hide the result/sampname column....

Last edited:
Oh wait, you want Access, hang on...............

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.

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

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!

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

1,219,819
Messages
6,150,402
Members
450,960
Latest member
GB2

### 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.

### Which adblocker are you using?

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

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