HomeGoogle DocsSpreadsheetHow to Use the Quartile Function in Google Sheets

How to Use the Quartile Function in Google Sheets

Published on

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 KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here