Excel using percentile - what does it exactly mean in simple terms?

[PCRIDE]

Can someone with some statistical background explain to me how Percentile works in Excel using numbers of Time.

For example, If I have 35000 orders and each order placed has a time elapsed between 6 min and 100 hours, but I want to file out what the average of hours an order takes using percentile.

In a large data set, its unfair to gauge the entire performance using Average, or even Median. My thought is using percentiles.

For example using the example above I have 35000 order (35000 line items in excel)
If I use Average, it might be 8 hours (Average across all items)
If I use Median its much lower at 20 min (Average of middle range)
Percentile 90th comes out to be 23 hours
Percentile 80th comes out to be 45 min
Percentile 70th comes out to be 25 min
Percentile 60th comes out to be 12 min

My question is why would the 90th percentile be so high and the 80th percentile have such a gap?

Does percentile mean, 90% of the time, Orders are completed in 23 hours like wise 80% of the time, orders are completed with in 45 min, or is it the INVERSE, is 90th actually 10%?

 

[DRSteele]

A percentile (or a centile) is a measure used in statistics indicating the value below which a given percentage of observations in a group of observations fall. For example, the 90th percentile is the value (or score) below which 90% of the observations may be found.

The term percentile and the related term percentile rank are often used in the reporting of scores from norm-referenced tests. For example, if a score is in the 90th percentile, it is higher than 90% of the other scores. The 25th percentile is also known as the first quartile (Q1), the 50th percentile as the median or second quartile (Q2), and the 75th percentile as the third quartile (Q3). In general, percentiles and quartiles are specific types of quantiles.

Now, did you actually apply the PERCENTILE function to your data and arrive at those example statistics you quoted?

 

[gsistek]

You're on the right track.
The 90th percentile means that 90% are below that threshold. So, 90% of the orders were less than 23 hours, 80% of the orders were less than 45 minutes, etc.
On a side note, the true median (middle value) is actually the 50th percentile (50% below, 50% above)

 

[Asala42]

Percentile is a statement about distribution.

60% of your data points are less than 12 minutes
70% of your data points are less than 25 minutes
80% of your data points are less than 45 minutes
90% of your data points are less than 23 hours


The distribution of time measurments like this are rarely Normal (parabolic curve around the average), usually they are weighted heavily towards the low end with the average being much higher than the median. What's happening here is 80% of your data falls within 45 minutes, but the next 10% (80-90th percentiles) have a much greater variation and can go into the 20+ hour range. The final 10% will contain your biggest outliers, and these skew average calculations heavily.

 

[PCRIDE]

Percentile is a statement about distribution.

60% of your data points are less than 12 minutes
70% of your data points are less than 25 minutes
80% of your data points are less than 45 minutes
90% of your data points are less than 23 hours


The distribution of time measurments like this are rarely Normal (parabolic curve around the average), usually they are weighted heavily towards the low end with the average being much higher than the median. What's happening here is 80% of your data falls within 45 minutes, but the next 10% (80-90th percentiles) have a much greater variation and can go into the 20+ hour range. The final 10% will contain your biggest outliers, and these skew average calculations heavily.

Ok this is good. So I can see better that 90% is 23 hours or less, in this case higher hours are bad = poor performance as we want orders to be processed faster, but looking at the 90th in the context of Test scores, that would make more sense.

I see how the 90th percentile alone doesn't really tell the story, if my average is 8 hours, thats still pretty poor, in other words I could say 80% of the orders are processed in 45 min or less, correct? Which is more positive than your "average" performance. So I almost want to start at the 25th percentile, then work my way up. This really shows the distribution of the performance which is what I want to see.

DRSteele - Yes those are actual results

gsistek - Yes Average of the middle range. - In a box plot is the length of the box the Upper and Lower Median? or just the range?

 

[DRSteele]

You might consider constructing a frequency histogram and see what you can glean from it. An analysis of the dispersion of the data wouldn't hurt either. If you've got some spare time and want to learn a bit about statistics, watch these 69 videos, which are immensely instructive. https://www.youtube.com/playlist?list=PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj

 

[Asala42]

My understanding of a box plot is that the lower bar of the box is at the 25th Percentile and the upper bar is the 75th (basically making the box the middle two quarters).

Putting those percentiles together pretty much tells the story. 80% of your orders are processed within 45 min, but the remaining 20% are processed longer - with the last 10% processed far longer. That raises a question about what's going on with those last 10-20%. My guess is there's a common issue or two at work there. A histogram may help, for example order type "xyz" takes at least 24 hours you could see a cluster points around the 24-25 hour mark.