QUARTILE in Google Sheets

What is QUARTILE Function in Google Sheets?

The QUARTILE function in Google Sheets returns the quartile value of a dataset. Here, quartile refers to one of the four equal parts. The outcome is based on the values in the range. Usually, quartiles divide any dataset into four intervals. Each quartile is said to consist of 25% of the data points. Using Quartile helps summarize data by identifying key percentiles. Some important quartiles include the median (second quartile). The QUARTILE in Google Sheets calculates the value in a dataset’s specific quartile. Besides QUARTILE, you also have two variations that differ in how they calculate the quartile values.

QUARTILE.INC – it uses the “inclusive” method, which includes the values when determining the boundaries for each quartile
QUARTILE.EXC – it uses the “exclusive” method, which excludes the boundary quartile value from the calculation

The example below shows the first quartile of some given values using the QUARTILE function.

QUARTILE in Google Sheets is used to calculate the quartiles of a dataset. These quartiles divide the data into four equal parts. Each part represents 25% of the data.
The syntax of the QUARTIEL function in Google Sheets is as follows:

=QUARTILE(data, quart)

data: This is the range for which you calculate the quartile.

quart – defines which quartile you want to calculate.

The QUARTILE function thus helps you divide your data into quartiles. The minimum and maximum quartiles (Q0 and Q4) give you the range of the dataset.
It is used in statistical analysis to understand the spread and distribution of the dataset.

Syntax

The QUARTILE function breaks down a dataset into quartiles. It provides useful information on the distribution of the data. Before deep-diving into the topic, let us check the Quartiles in Google Sheets formula.

=QUARTILE(data, quart)

data: The range for which you want to calculate the quartile.
quart: An integer value – 0, 1, 2, 3, or 4. It mentions which quartile to return.
0 = The “0th percentile”.
1 = First quartile or the 25th percentile.
2 = Second quartile or the 50th percentile.
3 = Third quartile or the 75th percentile.
4 = Maximum value is the “100th percentile.”

How to Use QUARTILE Function in Google Sheets?

We can use QUARTILE in Google Sheets in two ways. You can use these quartile calculations to analyze the deviations in your data useful for business or sales figures.

Entering the QUARTILE function Google Sheets manually
Through the Google menu bar

Entering the QUARTILE Function Google Sheets Manually.

Let us look at how to enter the function manually. Choose the range of cells containing the dataset you want to calculate the quartile.

Let us find the quartile for this set.

Step 1: To find the second quartile or the set’s median, type “=QUARTILE(.”

Step 2: Now, we enter a specific cell reference. Here, we enter the range A2:A11 just for reference. We enter 2 as the second argument for the second quartile and close the braces.

Step 3: Press the “Enter” key. You get the median or second quartile of the specified range. It marks the second quartile. It is the median, representing the middle point of the given values. Half of the values lie below 7.13, and half above it.

Using the Google Menu Bar

This is a method where you do not manually enter the function. First, go to the cell where you wish to enter the QUARTILE formula.
In the Google menu bar, click on “Insert” ➝ “Function” ➝ “Statistical” ➝ “QUARTILE.”
Enter the required arguments. Close the bracket and press the “Enter” key.

Examples

A quartile is a statistical term that helps understand how the data is spread. It divides a dataset into quarters, thereby giving four equal parts. There are three main quartiles:

First Quartile: This represents the first 25% of the data.
‍Second Quartile: This marks the first 50%.
Third Quartile: This marks 75% of the data.

Now, let us look at some examples of how the quartiles are useful for us.

Example #1

Let us have the following dataset of the monthly sales of some employees in Column A. Let us analyze this sales data. The aim is to calculate all the quartiles for these sales figures to understand how the data is distributed.

Step 1: Let us calculate the first quartile. This represents the point below which 25% of the sales data falls. Enter the following formula in cell B13.

For this dataset, the first quartile is $1,392.75.

Step 2: The second quartile is the median value, which divides the dataset into two halves. Enter the following formula in B14. =QUARTILE(B2:B11, 2)

This formula returns the median value of the sales data. For this dataset, the second quartile is $1,854.

Step 3: The third quartile is the value below which 75% of the sales data fall. Here, we enter the following cell in B15.

=QUARTILE(B2:B11, 3)

This formula returns the third quartile value for the sales data. For this dataset, the third quartile is $3428.50.

QUARTILE in Google Sheets Example 1-3.png

These results show that 25% of the employees had less than $1,392.75 in sales.
The median shows that half of the employees had sales below $1,854, and half had sales above.
The 75th percentile shows that 75% of the employees had sales less than $3428.50.

Example #2 – Determine Interquartile Range (IQR)

There’s no direct function for the interquartile range. This range is calculated by finding the 1st and 3rd quartiles to identify the IQR.

Let us use the following steps to calculate a dataset’s interquartile range (IQR) in Google Sheets. The Interquartile range Google Sheets value is an indication of how spread out the data is. It shows where half of the data points are located.

Step 1: First, let us enter a dataset’s values in a sheet as shown below. These values represent the grades of students in a class.

Step 2: Calculate the first and third quartiles as follows.

Next, we’ll use the QUARTILE function in Google Sheets to calculate the dataset’s first (Q1) and third (Q3) quartiles.

=QUARTILE(range, 1)

=QUARTILE(range, 3)

Step 3: We can subtract the first quartile from the third quartile to obtain the interquartile range. This range value is 16.

IQR=Q3−Q1

It tells us the spread of the middle 50% of values in our dataset.

The IQR or Interquartile Range is the difference between the first and third quartiles. These are the medians of the lower and upper halves of the data. It tells us about the spread of 50% of our values.

Example #3 – Using QUARTILE with IF Function

We calculate the third quartile based on a particular sales representative in this example. For this, you can use the IF function along with QUARTILE as follows:

We have the sales data of a few sales representatives. We must find the sales made by a representative, “John.”

Step 1: Enter the following function in cell B12.

=ARRAYFORMULA(QUARTILE(IF((A2:A10=”John”),B2:B10),3)

Explanation:

A2:A10=”John”: It checks if the sales rep’s name in column A is “John.”
A2:A10=”John”, B2:B10: The IF statement returns the sales values from column B for “John.”
QUARTILE(…, 3): The QUARTILE function calculates John’s sales’ third quartile (Q3)

Step 2: The formula will return the third quartile (Q3) sales specifically for John.

Here, the IF function inside QUARTILE filters the data before calculating the quartile based on a specific condition of a text value. The QUARTILE function calculates the required quartile of the filtered data which meets the condition specified in IF and displays the corresponding sales value.

These examples demonstrate how to use QUARTILE with IF in Google Sheets to analyze subsets of data based on specific conditions.

Important Things to Note

With QUARTILE, there is no need to sort the data manually. It will execute and give the result regardless of the order of the data.
QUARTILE in Google Sheets ignores empty cells in the range. However, text or non-numeric values will give an error.
It is very useful in determining the spread of the middle 50% of the data.
We get the middle value as the median for a dataset with an odd number of values, and the average of the two middle values will be the median if we have an even number of values.
You can combine the QUARTILE function with IF to calculate quartiles when the dataset meets specific conditions.

Frequently Asked Questions (FAQs)

What is percentile in the context of the QUARTILE function?

In any dataset, the quartiles divide data into four sections. They can also be referred to as percentiles of 25% (25% x 4 = 100%).

The first quartile is called the 25th percentile. Here, 25% of the data points lie within this.
The second quartile is the 50th percentile, as 50% of the data is below this point. It is also called the median.
The third quartile is the 75th percentile, as 75% of the data lies below this.

What is the difference between QUARTILE, QUARTILE.INC and QUARTILE.EXC?

1. The QUARTILE function in Google Sheets is the same as QUARTILE.INC, which includes the minimum and maximum values when calculating the quartiles.
2. To perform calculations for quartiles while excluding the minimum and maximum values, we use the QUARTILE.EXC function.

What is the interquartile range in Google Sheets?

The interquartile range or IQR helps us measure the spread of the middle 50% of the points in a dataset. It is calculated by subtracting the first quartile from the third quartile of a dataset Q3-Q1.

The IQR measures the spread of the middle 50% of the values and therefore, isn’t usually affected by very small or unusually large outliers. This makes it helpful in measuring the dispersion compared to a metric like the range.

Download Template

This article must help understand QUARTILE in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

QUARTILE In Google Sheets Download

QUARTILE in Google Sheets