Hi, this is Wayne again with a topic “The Excel Quartile Function”.
In this article i will show you how to use the quartile function in excel and the example i want to give is of a class and a list of test scores. So let’s say i just gave a test to this group of students and you can see i’ve calculated the top three scores. Bottom three scores the high score and the low score. If you want to learn how i did that you should watch my tutorial on the large and small functions in excel, but in this article we’re going to focus on how to draw even more information out of the data we have here on their test scores.
I would like to know what the first quartile of test results is, and the second quartile third and fourth, and that way i’ll know the scores that the students would have to receive in order to be in that fourth quartile or the third quartile etc. So it would be fairly complicated to do this, were it not for the excel quartile function. Let’S look at how it works, and i want to start with the first quartile so i’ll.
Just click on cell b10 and type equals quartile left parenthesis and you can see what excel is looking for here. It wants an array and then a comma and then quart for quartile. So by array it just means a range of cells. So i’m going to click here on g2 and drag down to select all of the test scores! That’S going to be my array next i’ll, just type comma, and then i just need to decide which quartile am i interested in here.
Well, i’m interested in the first quartile, because that’s the information i’m looking for in this particular cell, so i’ll just type a 1 and then the right parenthesis tap enter on the keyboard and it returns the number 71.75. So, knowing that the low score is 58, that tells us that, in order to be in the first quartile of scores, a student would have to score between 58 and 71.75, and what that means is that 25 of the results here fall in between 58 and 71.5. That’S the low quartile, the first quartile okay, what about the second quartile? Well, i could click on cell b8 type equals type, quartile and repeat the same steps basically, but it’s actually much easier and quicker to simply copy and paste the formula that we already have so i’ve clicked on b10, i’m going to hold ctrl and tap c to Copy that and i’ll paste it here for the second quartile, while i’m at it, might as well paste it in for the third quartile. Now, of course, it’s just giving me the same results. 71.75. The reason for that is because it still has the number one in the formula so to fix that all you have to do is click on the cell. In this case b8, it highlights the cell and up in the formula bar. I can easily change that one to a two, and now it should give me the second quartile when i tap enter and it does 83.5 now, let’s do the same thing for the third quartile i’ll.
Just click on b6 in this case, go up to the formula bar and change the one to a three tap enter, and now we have the third quartile. Now we already know what the high score is. The high score listed here is a hundred, and i figured that out by using a max function and formula, and so because of that we don’t really need the fourth quartile. The fourth quartile would just give us the same number. Okay, it would give us the highest score, the highest possible number. So, even though we don’t have to do a fourth quartile, let’s do it anyway, so once again i’ll just copy paste that same formula in, but this time, i’ll change the one to a four tap enter.
It gives me the same results, but now i’m using a different function. Instead of the max function, i’m using the quartile function, all right. We have all four quartiles and just as a review. What this information means is that, out of all of the test results, data 25 of that data falls between 58 and 71.5.
25 falls between 71.75 and 83.5. 25 falls between 83.5 and 94 and 25 falls between 94 and 100.. So any student that got a score of 94 through 100 made it into the fourth quartile of scores for the class. One last thing you might want to be aware of about the quartile function in excel is that you can also have it calculate the low score. Now i already have the low score calculated. I used a min function and formula to calculate the smallest number in this range, but i could have just used the quartile function, so, let’s switch it to quartile. I could type equals quartile left parenthesis, select the array or range type, comma and then, which quartile do. I want to put here one two, three or four.
Actually, none of the above i would put zero and then right, parenthesis tap enter and it returns the lowest number from this list. 58.. Thanks for watching this tutorial, i hope you found it to be helpful. If you did please like follow and subscribe and when you do click the bell so you’ll be notified.
Whenever i post another video, if you’d like to support my channel, you can do that through my patreon account and you’ll see a link to that in the description below speaking of patreon. I want to give a quick shout out to my five dollar patreon supporters. Thank you so much for all you do to support my channel. .