QUARTILE Function
Returns the quartile of a given data set
Returns the quartile of a given data set
The QUARTILE function is categorized under Statistical functions. The function will return the quartile of a given data set.
As a financial analyst, QUARTILE can be used to find out a specific percentage of incomes in a population. The function is very useful in revenue analysis.
=QUARTILE(array,quart)
The function uses the following arguments:
As a worksheet function, QUARTILE can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider a few examples:
Suppose we are given the following revenue figures:
QUARTILE can return the minimum value, first quartile, second quartile, third quartile, and max value. The function accepts five values for the quart argument, as shown below:
The formulas used are below:
Similarly, we used 1 as quart argument for the 25^{th} percentile, 2 for the 50^{th} percentile, 3 for the 75^{th} percentile and 4 for max value.
We get the results below:
If we wish to highlight cells by quartile, we can apply conditional formatting with a formula that uses the QUARTILE function. Suppose we are given the following data:
If we wish to highlight the minimum quartile, we can use conditional formatting. The formula to be used is:
We get the result below:
If we want, we can highlight every cell based on the quartile it belongs to. For that, we will set four conditional formatting rules:
The QUARTILE function is automatic and will calculate the 1st quartile with an input of 1, the 2nd quartile with an input of 2, and the 3rd quartile with an input of 3. With an input of 0, the function returns the minimum value in the data.
We need to arrange the conditional formatting rules so that they run in the same direction. The first rule highlights values greater than the 3rd quartile. The second rule highlights values greater than the 2nd quartile, the 3rd rule highlights data greater than the 1st quartile, and the last rule highlights data greater than the minimum value.
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly improve your financial modeling. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!