How to find the 90% time in a range
Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Aug 2013
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,376
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to find the 90% time in a range

    Quote Originally Posted by Fyrdawg View Post
    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. #3
    New Member
    Join Date
    Aug 2013
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,376
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to find the 90% time in a range

    Quote Originally Posted by Fyrdawg View Post
    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.

  5. #5
    New Member
    Join Date
    Aug 2013
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the 90% time in a range

    Thank you for the advice.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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