Statistical Measures: Percentiles and Quartiles Explained

Measures of position. We're going to take a look at two types of measuring position when it comes to statistics. Those two types of measures we're going to take a look at are percentile and quartiles.

Now percentiles you may remember from high school. You may remember that some students ranked on the 90th percentile and that doesn't necessarily mean they scored 90% on an exam. It means that in terms of their grades they are better than 90% of the students in the class.

So therefore they're in the 90th percentile. Now their scores may never reach 90. Let's say their scores are 80 and it's a really bad school and the grades are not that good.

Well if you score an 80 and everyone else scores way below that and that's most of the population, you're still in 90th percentile because you're better than 90% of the students whatever their score is. So percentile is what we're going to look at and then we'll look at quartile. Quartile is about breaking up those grades into 25% blocks.

So it could either be 25%, 50%, 75% and 100% or just focusing on three like 25%, 50% and 75%. Some measure in in that range. So let's take a look and see how these work.

We have sales and these sales could be in increments of a thousand. We have several sales people here and there are 23 and these are the sales for the four quarters. We want to get a sense of where people rank based on how they did in sales for quarter one, quarter two, quarter three and quarter four.

We can come up with a score that gives us a percentile for each of the quarters. We're going to use the percentile function. We don't have to worry about complicated math and we're also going to use conditional formatting.

If you can clearly see where people fall within the range. Now the conditional formatting is already done behind the scenes. If I select this range and I go to conditional formatting and choose manage rules, you'll see that we have the three types of percentiles that we're looking for.

Sales people who are at 10% or below, around 50% above and below and 90% and above. So you just have to take a look at our calculations here and see how we measure that. All right, so I'm going to close this and so we're going to calculate this for the entire range.

So I'm going to start with 10%. I'll type equal and we have a choice of either choosing percentile. Uh, well, when I type percentile, you'll see a percentile INC or percentile EXC.

Now, if I use INC, I'm including values for zero and a hundred percent. If I don't think that people are going to get those scores because those are the extremes, then I'll just choose EXC, which excludes zero and a hundred percent. And that's what I'm going to do here.

So I'm going to choose percentile EXC, and then I'm going to select the entire array. After selecting that array, I want to make sure to lock it because I'm going to autofill this down and I don't want that range to change when I use autofill. So I'll press F4.

I'll enter a comma and then I would like to see the score that represents 10% of all the scores that are here. So for the value for K, I will select 10% and then I'll press ENTER. And so 38.6 is the score for 10% and below.

Now I have to autofill this down to actually see conditional formatting applied to all the values. So I'll go here and I'll autofill this down and voila, we get to see the scores. If you review and take a look at the table, you'll see that 50% is sort of like the middle central tendency.

Green is really high and red is like the bottom 10% and below. So the conditional formatting really helps us see where the scores are in terms of percentile. And it looks like quarter three is doing much better than any of the other quarters.

All right, so that is percentile. The next thing we'd like to take a look at is quartile. And when working with quartile, we want to break up the percentile into quarters.

So quartile split the data into four equal parts. Quartile INC can be used to establish the minimum, the max, or the Q1 to Q4 values. Quartile EXC can be used to establish the Q1 to Q3 values.

It's zero exclusive, so you're not including zero. All right, so I'm going to go here and I want to use a mixed reference formula. This is one of the few places where we're going to use a mixed reference formula.

I'm going to select all the values here. And for our purposes, we're going to use quartile INC. We include values from zero to 25,25 to 50,50 to 75, and 75 to 100.

We're going to include zero. So the formula is going to be equal to quartile. That's what I'm going to type.

I'm going to type INC. Now I need to select the range. And the range is this column right here.

Now all these values are in a column. Usually we would lock the column. This is one of those rare exceptions where I want to lock the row.

Why? Because when I drag this down, I don't want the values to start moving down. So I'm going to lock the row for this. I'll press F4 once, twice.

Then I'm going to enter a comma and then I need to indicate what type of quartile that I want. And I'm going to go over here to the left and select the first quartile. Now this is a case where the values are in a column and I will lock the column.

Because when I drag this across, I don't want one to shift over into the table. I'll press F4 three times. One, two, three.

I'll enter a closed parenthesis. I've entered in my mixed referencing correctly. Now I'm free to press Control-Enter and take a look at the values.

And there I have my values for the quartiles for each of the quarters. If I take a look inside the formula, I'll see it is referencing each specific quarter. And for the most part, you can see that this is pretty accurate.

For the 50 percent quartile for the first column, I see 61.0. And over in the percentile here, I see 61.5. So very close. Pretty much the same for quarter two, but then it fluctuates depending on the scores for each of the quarters. These are individual scores for quarters.

It's not including all the values from the table. Now that I have this information here, I want to do some analysis. I may have a sense which week is best, but I want to create a special type of chart that's going to show me which week is better.

Now, if you haven't figured out already, it looks like three by far has the most values that are colored green. Q1 looks like it's doing the worst. It has a lot of red values.

And then everything else is just kind of average. So to bring this information to life, we're going to use a type of chart called the box and whisker chart. We're going to start with the first quarter.

I'm going to select the quartile values one to four for the first quarter. I'm then going to head over to insert from the insert tab. I want to take a look at the different chart types here.

Box and whisker is not one of the main popular types of charts we work with. So it's not going to be a column chart or a line chart or a pie chart. We're looking for box and whisker.

So box and whisker is going to be the dropdown that looks like a column chart. So I'm going to go over here, click the dropdown and look for box and whisker. I will select box and whisker and there is the chart.

Now we have some details in the spreadsheet that tell you the type of information you're looking at here. Each of the lines represent some kind of information. So the shaded box represents Q1 to Q4 data.

That's everywhere that's shaded in. The upper and lower lines represent the range. And if you hover your mouse over the upper line, you'll see a little pop up that comes up and that tells you that's 98.

That's the highest amount for quarter one. If I hover my mouse over the bottom line, I'll also see another pop up come up. That's the lowest point.

That's 40. That matches what I have here. The X signifies the mean.

If I hover my mouse over the X, the mean is 69.5. The line within the shaded box represents the median. If I hover my mouse over the line, that is 70. So there is a difference between the line and the X. Now to make a true comparison, I need to create another set of box and whisker charts for quarters two, three, and four.

I can do that by simply going over to the bottom corner of the range and dragging this across. And now I have all the information represented here. I'll make this a little larger so we can analyze this data.

Now you'll notice that when it comes to the values, that the X doesn't always line up with the line. So there is a difference between the mean and the median. And then these little zeros, those represent the values for 25% and 70, actually, yeah, 50% and 75%.

So for instance, for the first chart, if I go over to the first zero, that is 79, that represents the 75%. And then if I go over to the bottom zero here, I see 61, that represents the 50%. So how do we interpret this chart? How are we to read this? Well, you want to take a look at risk versus reward.

If you take a look at the first chart, there is a high potential for reward because you get the highest values with the first quarter. But the danger is you also have the highest risk because this also has the lowest value. If you're looking for a safe range of values, one that is more consistent, then looking at the chart, I would probably go with third quarter, which is great.

The lows are not as low as any of the other quarters, but the high is almost just as high as the first quarter. If anything, it would be in second place. So risk is considered bad.

Even though you may have a high reward, you want to be able to get values that are high, but not incur that much risk. And so that's how we can interpret the information when it comes to quartiles. Here, we're imagining these are sales, but there is another example where you can imagine these are grades.

And so that is measures of position. You're ranking values against each other and breaking them up into either percentiles or quartiles. Thank you for watching.