How to Use the Quartile Function in Google Sheets

First of all, let me explain the benefit of using the Quartile function in Google Sheets over finding the quartiles manually.

To find the quartiles manually, the data must be ordered (sorted) from least to greatest. But you can use the Quartile function in Google Sheets in a sorted or unsorted dataset.

Quartiles are actually three cut off points in an array or range. Mark the data in the middle (median), that results in two halves. Mark the middle of each half again. So you will get the three cut off points (quartiles).

There are five quartile numbers that you can use in the Quartile function and they are 0, 1, 2, 3 and 4.

In this, the quartile number 0 returns the minimum value (similar to the MIN function) in the dataset and the number 4 returns the maximum (similar to the Max function) value. See the syntax below to understand about the ‘quartile numbers’ used in the function.

The Syntax of the Quartile Function in Google Sheets:

QUARTILE(data, quartile_number)

Arguments:

data – The array/range containing the dataset to consider.

quartile_number – Which quartile value to return (numbers 0 to 4).

0Returns the minimum value in the array (MIN).
1Returns the value closest to the first quartile in the array.
2Value in the array closest to the median (MEDIAN).
3Value in the array closest to the third quartile.
4Returns the maximum value in the array (MAX).

Examples to the Use of the Quartile Function in Google Sheets

I have used all the quartile_numbers in the below examples. Please see the screenshot for the formulas in cell D2 to H2 and the description scribbled in blue ink just below that.

Example to the Quartile Function in Google Sheets

As you can see the MIN, MEDIAN and MAX formulas can replace the quartile numbers 0, 2 and 4 respectively.

Let me show you how to find the Quartiles manually in Google Sheets. I am using the same above dataset for the examples.

Finding Quartiles Manually in Google Sheets

I am leaving quartile numbers 0 and 4 as it’s min and max. Let me begin with the median which is quartile number 2.

You can find the median of a dataset manually by eliminating each number, one from the beginning of the dataset and another from the end until you find the middle of the data set. Of course, the data must be sorted before doing this.

We can visualize that as below.

Easiest way to find the Median Manually

While eliminating the numbers, finally if you end up in two numbers, find the average of that numbers. It is not applicable in the above example.

Since we have the median (quartile 2) it is easy to find the quartile 1 and 3. Take each half and find the median of them.

Finding quartiles manually in Sheets

That’s all about the use of the QUARTILE statistic function in Docs Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.