Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

QUARTILE Function

Returns the quartile of a given data set

What is the QUARTILE Function?

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.

 

Formula

=QUARTILE(array,quart)

 

The function uses the following arguments:

  1. Array (required argument) – It is the array or cell range of numeric values for which we want the quartile value.
  2. Quart (required argument) – It indicates which value will be returned. It is an integer between 0 and 4, representing the required quartile.

 

How to use the QUARTILE Function in Excel?

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:

 

Example 1

Suppose we are given the following revenue figures:

 

QUARTILE Function

 

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:

 

QUARTILE Function - Example 1

 

The formulas used are below:

 

QUARTILE Function - Example 1a

 

Similarly, we used 1 as quart argument for the 25th percentile, 2 for the 50th percentile, 3 for the 75th percentile and 4 for max value.

We get the results below:

 

QUARTILE Function - Example 1b

 

Example 2

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:

 

QUARTILE Function - Example 2

 

If we wish to highlight the minimum quartile, we can use conditional formatting. The formula to be used is:

 

QUARTILE Function - Example 2a

 

We get the result below:

 

QUARTILE Function - Example 2b

 

If we want, we can highlight every cell based on the quartile it belongs to. For that, we will set four conditional formatting rules:

  1. =A1>QUARTILE(A1:E5,3)
  2. =A1>=QUARTILE(A1:E5,2)
  3. =A1>=QUARTILE(A1:E5,1)
  4. =A1>=QUARTILE(A1:E5,0)

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.

 

Things to remember about the QUARTILE Function 

  1. #NUM! error – Occurs if either:
    1. The given value of quart is < 0 or > 4
    2. The given array is empty
  2. #VALUE! error – Occurs if the given value of quart is non-numeric.

 

Click here to download the sample Excel file

 

Additional resources

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:

  • Excel Functions for Finance
  • List of Excel Functions
  • Financial Modeling Guide
  • Financial Modeling Courses

Free Excel Tutorial

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!