90th percentile with conditions

[jodily]

Hi,
I need to find out the 90th percentile of a set of times but with conditions. I have rows with data that need to be considered in the percentile. The first column has the incident numbers (there are duplicate values because there are different staff showing up at different times for the same incident). The second column has the times the staff showed up and the third column has the amount of staff that showed up each time. I only want to consider the times in the percentile that incorporate the first 12 staff, this may just be the first 3 times if 4 staff show up each time. However I am not sure how to create the formula that will find the times of the first 12 staff members and ignore the other times of any staff over 12. I need a formula that will copy down because I have data for about 300 different incidents to look at. Hopefully there is a way to do this.

Incident Response times # of staff
HF16-2 0:11:36 4
HF16-2 0:05:48 4
HF16-2 0:06:56 2
HF16-2 0:03:20 4
HF16-2 0:02:19 4
HF16-2 0:08:40 1
HF16-3 0:12:05 4
HF16-3 0:02:03 4
HF16-3 0:08:45 1
HF16-3 0:03:54 4
HF16-3 0:07:40 2
HF16-3 0:05:11 4
HF16-4 0:58:26 0
HF16-4 0:05:40 0
HF16-4 0:06:04 0
HF16-5 0:06:17 2
HF16-5 0:04:50 2

Thank you.​

 

[shg]

So for your example, you want the 90th percentile of these two time?

[Table="width:, class:grid"][tr][td="bgcolor:#F3F3F3"]

Inc​

[/td][td="bgcolor:#F3F3F3"]

Time​

[/td][td="bgcolor:#F3F3F3"]

Peeps​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td]HF16-2[/td][td]

0:11:36​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-2[/td][td]

0:05:48​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-2[/td][td]

0:06:56​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-2[/td][td]

0:03:20​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

0:03:20​

[/td][/tr]
[tr][td]HF16-2[/td][td]

0:02:19​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-2[/td][td]

0:08:40​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-3[/td][td]

0:12:05​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-3[/td][td]

0:02:03​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-3[/td][td]

0:08:45​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-3[/td][td]

0:03:54​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

0:03:54​

[/td][/tr]
[tr][td]HF16-3[/td][td]

0:07:40​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-3[/td][td]

0:05:11​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-4[/td][td]

0:58:26​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-4[/td][td]

0:05:40​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-4[/td][td]

0:06:04​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-5[/td][td]

0:06:17​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[tr][td]HF16-5[/td][td]

0:04:50​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"][/td][/tr]
[/table]

 

[jodily]

No, sorry. I need to calculate the 90th for each incident separately, so HF16-2 separately from HF16-3 and so forth. But within HF16-2 I need to just isolate the times for the first 12 people. Visually looking at it 4 people showed up at 2:19, 4 at 3:20 and 4 at 5:48, so those are the first 12 people and I just want the 90th for those first 3 times. So for each incident I need to somehow pull out the times of the first 12 people. I can certainly do it if I look at each incident separately but I have over 300 incidents to look at. Not sure if there is a way to do this with a formula.

So for your example, you want the 90th percentile of these two time?

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #F3F3F3"]

Inc​

[/TD]
[TD="bgcolor: #F3F3F3"]

Time​

[/TD]
[TD="bgcolor: #F3F3F3"]

Peeps​

[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD]HF16-2[/TD]
[TD]

0:11:36​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-2[/TD]
[TD]

0:05:48​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-2[/TD]
[TD]

0:06:56​

[/TD]
[TD]

2​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-2[/TD]
[TD]

0:03:20​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #E5E5E5"]

0:03:20​

[/TD]
[/TR]
[TR]
[TD]HF16-2[/TD]
[TD]

0:02:19​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-2[/TD]
[TD]

0:08:40​

[/TD]
[TD]

1​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-3[/TD]
[TD]

0:12:05​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-3[/TD]
[TD]

0:02:03​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-3[/TD]
[TD]

0:08:45​

[/TD]
[TD]

1​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-3[/TD]
[TD]

0:03:54​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #E5E5E5"]

0:03:54​

[/TD]
[/TR]
[TR]
[TD]HF16-3[/TD]
[TD]

0:07:40​

[/TD]
[TD]

2​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-3[/TD]
[TD]

0:05:11​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-4[/TD]
[TD]

0:58:26​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-4[/TD]
[TD]

0:05:40​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-4[/TD]
[TD]

0:06:04​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-5[/TD]
[TD]

0:06:17​

[/TD]
[TD]

2​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
[TR]
[TD]HF16-5[/TD]
[TD]

0:04:50​

[/TD]
[TD]

2​

[/TD]
[TD="bgcolor: #E5E5E5"][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Sort by Incident and Time ascending, then

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Incident​

[/td][td="bgcolor:#F3F3F3"]

Time​

[/td][td="bgcolor:#F3F3F3"]

Peeps​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]HF16-2[/td][td]

0:02:19​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][td]D2 and down: {=IF((SUMIF(A$1:A2, A2, C$1:C2) >= 12) * (SUMIF(A$1:A1, A2, D$1:D1) = 0), PERCENTILE(IF(A$1:A2 = A2, B$1:B2), 90%), "")}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]HF16-2[/td][td]

0:03:20​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]HF16-2[/td][td]

0:05:48​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

0:05:18​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]HF16-2[/td][td]

0:06:56​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]HF16-2[/td][td]

0:08:40​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]HF16-2[/td][td]

0:11:36​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]HF16-3[/td][td]

0:02:03​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]HF16-3[/td][td]

0:03:54​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]HF16-3[/td][td]

0:05:11​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

0:04:56​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]HF16-3[/td][td]

0:07:40​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]HF16-3[/td][td]

0:08:45​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]HF16-3[/td][td]

0:12:05​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]HF16-4[/td][td]

0:05:40​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]HF16-4[/td][td]

0:06:04​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]HF16-4[/td][td]

0:58:26​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]HF16-5[/td][td]

0:04:50​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]HF16-5[/td][td]

0:06:17​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[/table]

 

[jodily]

Thank you, I appreciate it! I will give it a try in a bit.