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

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.

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.

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.

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