A.N.M. Mohaimen Shanto
The QUARTILE function is a built-in function in Excel that is categorized as a Statistical Function. Quartiles are values that split your data into quarters. It divides your data into four segments according to where the numbers fall on the number line. It is one of those essential functions. In this tutorial, you will learn every detail of the QUARTILE function in Excel. This tutorial will be on point with suitable examples and proper illustrations.
The above screenshot is an overview of the article. It represents a simplified application of the QUARTILE function in Excel. You’ll learn more about the dataset as well as the methods and procedures under different criteria in the following sections of this article.
Download Practice Workbook
Download this practice workbook.
Introduction to QUARTILE Function
⏺ Function Objective
Find the quartile (each of four equal groups) for a conveyed set of data.
⏺ Syntax
⏺ Arguments Explanations
| Arguments | Required/Optional | Explanation |
|---|---|---|
| array | Required | The array or cell range of numeric values for which you want to calculate the quartile value. |
| quart | Required | Indicates which value to return.
|
⏺ Returns
The QUARTILE function returns a numeric value. The quartile of the dataset.
⏺ Available in
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.
How to Use QUARTILE Function in Excel
Basically, to use the QUARTILE function in Excel, we first select a range from a dataset to analyze. Then we have to enter the quart values. Now, we have already shown the quart values and their meaning.
Before we show you the procedures, let’s have a quick look:
- The first quartile means the 25th percentile – as 25% of the data is lower than this value.
- The second quartile is the median of the dataset, we can also call it the 50th percentile as 50% of the data is lower than this.
- The third quartile means the 75th percentile, as 75% of the data is lower than this value.
Take a look at this dataset:
Here, you can see, we have some random values. Now, we are going to find the first quartile (25th percentile) of this dataset.
📌 Steps
- First, type the following formula in Cell D5:
=QUARTILE(B5:B10,1)- After that, press Enter.
As you can see, we have successfully calculated the first quartile of the dataset.
Similarly, you can find the rest of the quartiles:
Read More: How to Use MEDIAN Function in Excel (4 Suitable Examples)
5 Practical Examples with QUARTILE Function in Excel
In the following sections, we will provide you with five suitable and useful examples that will clear your idea about this function. We recommend you learn and practice all these examples to enrich your Excel knowledge.
1. Use of QUARTILE Function to Find Quartiles of Salaries
In this example, we will guide you to calculate the 50th percentile or the second quartile from a range of salaries. It will be a pretty simple example. But it will help you in a lot of scenarios.
Take a look at the following dataset:
Here, we have some persons’ names and their salaries. Now, our goal is to find the second quartile or the 50th percentile of the salaries.
📌 Steps
- First, type the following formula in Cell E5:
=QUARTILE(C5:C10,2)- After that, press Enter.
As you can see, we have successfully used the QUARTILE function of Excel to find the second quartile.
Read More: How to Use PERCENTILE Function in Excel (With an Example)
2. QUARTILE Function to Find Quartiles of Marks in Excel
Similar to the previous method, we are going to use the QUARTILE function here.
Now, take a look at the following dataset:
Here, we have some students’ marks. Now, our goal is to find the 3rd quartile of this dataset using the QUARTILE function.
📌 Steps
- First, type the following formula in Cell E5:
=QUARTILE(C5:C10,3)- After that, press Enter.
As you can see, we have successfully used the QUARTILE function of Excel to find the third quartile.
Similar Readings
- How to Use Excel STDEV Function (3 Easy Examples)
- Use Excel GROWTH Function (4 Easy Methods)
- How to Use Excel FREQUENCY Function (6 Examples)
- Use TREND Function in Excel (3 Examples)
- How to Use Excel NORMDIST Function (2 Applications)
3. Find Quartiles for All the “quart” Values
Now, we already know all the possible quart values and their meaning. Now, we are going to use the QUARTILE function to find all the quartile values from the previous dataset.
To demonstrate this, we are going to use the following dataset:
Follow these simple steps to find all the quartiles.
📌 Steps
- First, type the following formula in Cell G5:
=QUARTILE($C$5:$C$10,E5)Here, we are using the Cell reference in the quart argument.
- Then, press Enter.
- Now, drag the Fill handle icon over the range of cells G6:G9.
In the end, we are successful to find all the QUARTILE values for all the quart values in Excel.
4. Examples of QUARTILE.INC & QUARTILE.EXC in Excel
After Excel 2007, Microsoft substituted the QUARTILE function with its two new variations: QUARTILE.INC and QUARTILE. EXC.
The QUARTILE function still works in Excel, You can use it in newer versions as well. In our opinion, you should use QUARTILE.INC or QUARTILE.EXC wherever possible. We don’t know when Microsoft will drop the QUARTILE function.
Now, the QUARTILE.INC works as the same as the QUARTILE function. The “INC” means in the function is “inclusive”. This means that QUARTILE.INC contains the minimum and maximum values when computing the quartiles in the data range.
Now, we are going to use this in the previous dataset. Just type the following formula in Cell G5:
=QUARTILE.INC($C$5:$C$10,E5)Then, you drag the Fill handle icon over the range of cells G6:G9, it will show the following:
Now, the QUARTILE.EXC works almost similar to the QUARTILE.INC function. The basic difference is, it excludes the minimum and maximum values while calculating the quartiles from the given range.
Now, type the following formula in Cell G5:
=QUARTILE.EXC($C$5:$C$10,E5)After that, when you drag the Fill handle icon over the range of cells G6:G9, it will show the following:
As you can see from the example, we have used the QUARTILE.INC and QUARTILE.EXC functions in the same scenarios in Excel.
Read More: How to Use VAR Function in Excel (4 Examples)
5. QUARTILE Function in VBA
Now, you can also use the QUARTILE function in Excel. If you are a VBA freak like me, you will like this. Here, we are showing you some simple steps to show you the QUARTILE function in VBA.
📌 Steps
- First, press Alt+F11 on your keyboard to open the VBA editor.
- Then, click on Insert > Module.
- After that, type the following code:
Sub quartile_method()
Dim my_array
Dim wf As WorksheetFunction
my_array = Array(10, 4.7, 13.98, 12, 23.9, 16)
Set wf = Application.WorksheetFunction
MsgBox wf.Quartile(my_array, 2)
End Sub - Now, save the file.
- After that, click on Run. It will show you this message box.
As you can see, we have successfully used the QUARTILE function in VBA codes.
💬 Things to Remember
✎ The QUARTILE Function divides the dataset into four equivalent parts
✎ It will show #NUM! error if the quart value is less than 0 or more than 4.
✎ If your given array is empty, it will also show #NUM! error.
✎ It will return #VALUE! error if the given value of quart is non-numeric
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the QUARTILE function in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
Related Articles
- How to Use Excel SLOPE Function (5 Quick Examples)
- FORECAST Function in Excel (with other Forecasting Functions)
- How to Use LINEST Function in Excel (4 Suitable Examples)
- Use CORREL Function in Excel (3 Examples and VBA)
- How to Use MAX Function in Excel (6 Examples)
- Use MIN Function in Excel (5 Relevant Examples)
- How to calculate Average, Median, & Mode in Excel
