Calculating 95th percentile on grouped data

[dimsum1]

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>

 

[ranman256]

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]

Excel 2010
	A	B	C	D	E	F
1	Location	Type	ana	sampname	result	95th
2	Loc1	Wet	ana1	Sample1	12	42.4
3	Loc1	Wet	ana2	Sample1	3	22
4	Loc1	Wet	ana3	Sample1	15	24.5
5	Loc1	Wet	ana1	sample2	44	42.4
6	Loc1	Wet	ana2	sample2	23	22
7	Loc1	Wet	ana3	sample2	25	24.5
8	Loc1	Wet	ana4	sample2	12	12
9	Loc1	Dry	ana1	sample3	44	42.2
10	Loc1	Dry	ana2	sample3	55	56.9
11	Loc1	Dry	ana3	sample3	76	74.5
12	Loc1	Dry	ana1	sample4	8	42.2
13	Loc1	Dry	ana2	sample4	57	56.9
14	Loc1	Dry	ana3	sample4	46	74.5
15	Loc1	Dry	ana4	sample4	99	99
16	Loc2	Wet	ana1	sample5	124	755.75
17	Loc2	Wet	ana2	sample5	66	86.9
18	Loc2	Wet	ana3	sample5	6	728.95
19	Loc2	Wet	ana1	sample6	789	755.75
20	Loc2	Wet	ana2	sample6	88	86.9
21	Loc2	Wet	ana3	sample6	767	728.95
22	Loc2	Wet	ana4	sample6	565	565
Sheet1 (3)

Cell Formulas
Range	Formula
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
	A	B	C	D	E	F
1	Location	Type	ana	sampname	result	95th
2	Loc1	Wet	ana1	Sample1	12	42.4
3	Loc1	Wet	ana2	Sample1	3	22
4	Loc1	Wet	ana3	Sample1	15	24.5
8	Loc1	Wet	ana4	sample2	12	12
9	Loc1	Dry	ana1	sample3	44	42.2
10	Loc1	Dry	ana2	sample3	55	56.9
11	Loc1	Dry	ana3	sample3	76	74.5
15	Loc1	Dry	ana4	sample4	99	99
16	Loc2	Wet	ana1	sample5	124	755.75
17	Loc2	Wet	ana2	sample5	66	86.9
18	Loc2	Wet	ana3	sample5	6	728.95
22	Loc2	Wet	ana4	sample6	565	565
Sheet1 (3)

Cell Formulas
Range	Formula
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....

 

[sheetspread]

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

 

[dimsum1]

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]

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]

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]

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