90th percentile with conditions

J

jodily

New Member
Joined
Feb 23, 2018
Messages
5
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.​
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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]
 
Upvote 0
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.






shg said:
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]
Click to expand...
 
Upvote 0
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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Return last date checked
Replies
3
Views
56
Peter_SSs
Peter_SSs
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
316
dshtesy
D
S
  • Question Question
List all staff names that worked under a manager on a certain date
Replies
5
Views
340
Fluff
Fluff
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
Tara_A_Data
T
D
  • Solved
Help with Filter function
Replies
2
Views
120
Dazzybeeguy
D

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
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