# Thread: How to find the 90% time in a range Thanks: 0 Likes: 0

1. ## How to find the 90% time in a range

 0:06:42 0:06:11 0:04:11 0:06:15 0:06:57 0:06:00 0:06:23 0:10:15 0:06:54 0:06:20 0:06:06 0:04:15 0:10:59 0:05:08

Hi. Im trying figure out what time would be the 90th percentile of these times. Im using this formula: =PERCENTILE(W3:W29,0.9), and get an answer of 09:16, which seems way to high, since there are only two times above 6 minutes. When I just do the simple average of these times, I get 06:37, which seems closer to the correct answer. Any help is greatly appreciated.  Reply With Quote

2. ## Re: How to find the 90% time in a range Originally Posted by Fyrdawg Im trying figure out what time would be the 90th percentile of these times. Im using this formula: =PERCENTILE(W3:W29,0.9), and get an answer of 09:16, which seems way to high, since there are only two times above 6 minutes. When I just do the simple average of these times, I get 06:37, which seems closer to the correct answer.
You don't seem to understand what a percentile is. I wonder if you want something else altogether.

To understand percentiles in general and the result of Excel PERCENTILE in particular, it might help to look at a sorted list of your data.

 A B C D E 1 Data Rank 2 0:04:11 1 90% rank 12.60 =14*90% 3 0:04:15 2 90 %ile 0:08:56 =A13+(A14-A13)*MOD(D2,1) 4 0:05:08 3 PERCENTILE 0:09:16 =PERCENTILE(A2:A15,90%) 5 0:06:00 4 6 0:06:06 5 7 0:06:11 6 8 0:06:15 7 9 0:06:20 8 10 0:06:23 9 11 0:06:42 10 12 0:06:54 11 13 0:06:57 12 14 0:10:15 13 15 0:10:59 14

The 90%ile is the value that 90% of the data less than.

Since you have 14 values, the 90%ile should be a value between the 12th and 13th values, since 14*90% is 12.6 (D2).

Conceptually, it should be a value larger than 0.6 of the distance between 0:06:57 and 0:10:15, the 12th and 13th values. That is 0:08:56 (D3).

Excel PERCENTILE returns 0:09:16 (D4), which is close.

The difference is due, in part, to differences in the definition of percentile. My method was intended to be conceptual, not necessarily the correct method statistically.  Reply With Quote

3. ## Re: How to find the 90% time in a range

joeu2004,
Actually your explanation did a great job of telling me what I was looking for.. So I need a formula to compute the 90%ile of the data I posted above. I am not able to rank the data like you did in you example.

Thank you again for the help.  Reply With Quote

4. ## Re: How to find the 90% time in a range Originally Posted by Fyrdawg joeu2004, Actually your explanation did a great job of telling me what I was looking for.. So I need a formula to compute the 90%ile of the data I posted above. I am not able to rank the data like you did in you example.
Ostensibly, =PERCENTILE(range,90%) is that formula. The PERCENTILE function effectively sorts and ranks the data for us. So, do you still have a problem?

I just noticed that your example has 14 values, but your PERCENTILE formula references a range of 17 cells. Nevertheless, PERCENTILE returned the correct answer for those 14 values. So I presume the excess cells are empty or text (e.g. the null string "").

One issue to consider: the difference between PERCENTILE.INC (aka PERCENTILE) and PERCENTILE.EXC. See the help pages; or experiment.

PS: Even statiscians do not agree on the definition of percentile and how it should be calculated.  Reply With Quote

5. ## Re: How to find the 90% time in a range

Thank you for the advice.  Reply With Quote

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•