QUARTILE Function in Excel (Table of Contents)
QUARTILE in Excel
When you need to divide sales data or survey data into groups or you are working on populations data and need to divide that data in some percentage. In these cases, QUARTILE function is very useful. This function is very useful to find out a specific percentage of incomes in a population. Excel QUARTILE Function is also used in revenue analysis.
What is QUARTILE Function?
The QUARTILE Function returns the quartile for a given set of data. This function divides the data set into four equal groups. QUARTILE will return the minimum value, first quartile, second quartile, third quartile, and maximum value.
The QUARTILE Function is a built-in function in excel and it falls under Statistical functions category. This function is also known as a Worksheet function in excel. As a worksheet function, this function can be used as a part of the formula in a cell of a worksheet.
QUARTILE Formula in Excel
Below is the QUARTILE Formula:
Where the supplied arguments are:
- Array – It is the array or cell range of numerical values for which we want the quartile value.
- Quart – The Quartile value to return.
The Quartile function in Excel accepts 5 values as Quart( Second argument) which is shown in below table:
QUART Value |
Return Value |
0 | Minimum Value |
1 | First Quartile – 25th percentile |
2 | Second Quartile – 50th percentile |
3 | Third Quartile – 75th percentile |
4 | Maximum Value |
As a Worksheet function in excel, QUARTILE function can be used as part of a formula like below:
Where the QUARTILE Function found in Excel?
The QUARTILE Function is a built-in function in excel, hence it is found under FORMULAS tab. Please follow below steps:
- Click on FORMULAS tab. Click on More Functions option.
- It will open a drop-down list of functions category.
- Click on Statistical Functions category. It will open a drop-down list of functions.
- Select the QUARTILE Function from the drop-down list.
- In the above screenshot, as we can see there are two functions listed with the name of QUARTILE:
- QUARTILE.EXC
- QUARTILE.INC
In the new version of excel, the QUARTILE function has been replaced with these above two functions which give more accuracy in the result.
4.8 (2,665 ratings)
View Course
However, in excel the QUARTILE function is still available but it might be replaced in future with these functions any time.
How to use QUARTILE Function in Excel?
QUARTILE Function is very simple to use. Let us now see how to use QUARTILE function in Excel with the help of some examples.
QUARTILE in Excel – Example #1
We have given some sales figure:
Here, we will calculate the minimum value, First Quartile, Second Quartile, Third Quartile, and Maximum value by using the QUARTILE Function. For this calculation, we will take the value of second argument QUART from 0-4 of the QUARTILE function like below screenshot.
Now we will apply the QUARTILE function to the sales data.
We will calculate the QUARTILE function for all values one by one by selecting the values from a drop-down list.
The Result is:
Similarly, we will find other values. The final result is shown below:
Explanation:
- Pass the array or cell range of data values for which you need to calculate the QUARTILE function.
- Pass the value 0 as the second argument to calculate the minimum value of the given data set.
- Enter the value 1 for the first quartile.
- Enter the value 2 for the second quartile.
- Enter the value 3 for the third quartile.
- Enter the value 4 as the second argument which will give the maximum value of the data set.
QUARTILE in Excel – Example #2
Let’s take another data set in which data is arranged in ascending order for better understanding the use of Excel QUARTILE function:
Now will calculate the below quartile values:
The formula is shown as below:
We will calculate the QUARTILE function for all values one by one by selecting the values from a drop-down list.
The Result is:
Similarly, we will find other values. The final result is shown below:
Explanation:
- If we see the result closely, 1 is the minimum value from the given data set.
- 100 is the maximum value from the data set.
- 1^{st} quartile value is 28.75, 2^{nd} quartile value is 60 and 3^{rd} quartile value is 79.25.
- The QUARTILE function divides the data set into 4 equal parts. Means, we can say that 25% of the values fall between a minimum value and 1^{st}
- 25% values lie between 1^{st} and 2^{nd}
- 25% values lie between 2^{nd} and 3^{rd}
- 25% values lie between 3^{rd} and maximum value.
Now we will learn the use of QUARTILE.EXC and QUARTILE.INC functions as in future the QUARTILE function can be replaced by these functions.
The SYNTAX of QUARTILE.EXC and QUARTILE.INC is the same. If we select QUARTILE.EXC function from the Statistical functions category drop-down list of functions, it will open a dialog box for Function arguments.
The Passing arguments are same as QUARTILE function.
If we select QUARTILE.INC function, it will also open a dialog box for function arguments as shown below:
The QUARTILE.INC function returns the quartile of a data set, based on percentile values from 0..1, inclusive. Whereas the QUARTILE.EXC function returns the quartile of a data set, based on percentile values from 0..1, exclusive.
Now in below examples, will see the difference between the above two functions.
QUARTILE in Excel – Example #3
Let’s assume the below test scores of a class.
We will apply the QUARTILE.EXC and QUARTILE.INC functions on the above scores and will observe closely the result of these functions.
First, we are calculating the QUARTILE.INC function. Pass the list of scores as the first argument of the function and choose the second argument from the list 0-4 for a min, 1^{st}, 2^{nd}, 3^{rd}, and max values one by one as shown below:
We will calculate the QUARTILE function for all values one by one by selecting the values from a drop-down list.
The Result is:
Similarly, we will find other values. The final result is shown below:
The same steps will follow for calculating the QUARTILE.EXC function.
QUARTILE.EXC function 1^{st}, 2^{nd} and 3^{rd} quartile values.
The Result is :
Explanation:
- If you see the above results for values 0 and 4, the QUARTILE.EXC function returns the #NUM! an error value, because it calculates only 1^{st}, 2^{nd} and 3^{rd} quartile values. Whereas the QUARTILE.INC function works like same as QUARTILE function.
- The 2^{nd} quartile value is same for both the functions.
- The 1^{st} quartile value of QUARTILE.EXC will be little less than the QUARTILE.INC.
- The 3^{rd} quartile value of QUARTILE.EXC will be little higher than the QUARTILE.INC.
Things to Remember about QUARTILE Function in Excel
- QUARTILE Function divides the dataset into 4 equal parts.
- We can say that 25% values lie between Min and 1^{st} Quartile, 1^{st} and 2^{nd} Quartile, 2^{nd} and 3^{rd} Quartile and 3^{rd} & maximum value.
- If the function returns #VALUE! error – means the second parameter QUART of the function is non-numeric.
- If the function returns #NUM! error, i.e.
- The given array is empty.
- The value of Quart is less than 0 or greater than 4.
Recommended Articles
This has been a guide to Excel QUARTILE function. Here we discuss the QUARTILE Formula and how to use QUARTILE Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –