How to find the 90% time in a range

[Fyrdawg]

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 <tbody> </tbody> 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.

<tbody>
</tbody>

 

[joeu2004]

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

<tbody>
</tbody>

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.

 

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

Thank you again for the help.

 

[joeu2004]

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.

 

[Fyrdawg]

Thank you for the advice.