The quartile function in Google Sheets makes it simple to calculate quartiles without having to manually reorder your data from smallest to largest.
Quartiles are markers at three points in a dataset. They include:
- The median (middle number)
- The halfway point between the minimum number and median
- The halfway point between the median and the maximum number
The quartile function in Google Sheets also uses the min and max numbers in the dataset.
Therefore, the quartile function uses five quartile numbers to identify each marker including the minimum, maximum, median, and two quartile points.
The numbers used are 0,1, 2, 3, and 4 where 0 returns the minimum value and 4 is the maximum.
Quartile Function Syntax For Google Sheets
The function will send back the closest value to a specified quartile in your dataset.
The syntax used for this formula is:
- Data – The range or array of data to be considered for the calculation
- Quartile_number – Which quartile number between 0 and 4 to send back
- 0 – Gives the minimum value which is the same as using the MIN function
- 1 – Gives the value for the first quartile in the dataset
- 2 – Gives the value of the median
- 3 – Gives the value of the second quartile
- 4 – Gives the maximum value which is the same as using the MAX function
An Example of Quartiles in Google Sheets
In the above example, you can see we’ve used the syntax =Quartile(data, quartile_number) in which the data value has been substituted with the data set of A2:A8, and each quartile number from 0-4 has been represented too.
You just have to put the data set as the first argument and the quartile number as the second. It’s a fairly simple function so long as you remember which number from 0 to 4 represents each quartile marker.
The Complete Breakdown
In the above example, to get the results in the boxes you would have to follow these steps:
- Click the cell you want the result to show in. B2 in the example.
- Type =qu then click on the quartile function
- Click and drag over the desired dataset or manually type in the array.
- Type , on your keyboard
- Enter the desired quartile number from 0 to 4 and press the end bracket key on your keyboard
- Press enter
Quartile Function FAQs
Is the Quartile Function in Google Sheets Compatible with Microsoft Excel?
Microsoft has replaced the quartile query with quartile.inc and quartile.exc in Excel. The original quartile function still works, but it may be best to use one of the other functions just in case Microsoft makes it incompatible in the future.
If you plan to use your spreadsheet across platforms you will need to keep this in mind. These newer functions are both available in Google Sheets, so you don’t need to stress.
Quartile.inc uses the exact same syntax as the quartile function we already discussed. It uses the values of 0 to 4 to represent each quartile inclusive of the minimum and maximum values.
The syntax for quartile.exc is similar but only uses the values of 1 to 3 as it doesn’t recognize the minimum and maximum numbers of the dataset as part of its function.
That means you will get a completely different result if you use the .exc version.
To make it simple, just remember the quartile.inc function includes the min and max of the dataset and the quartile.exc function excludes them.
What Do Percentiles Mean When Talking About Quartiles?
As quartiles divide data up into 4 sections they are sometimes referred to as percentiles in groups of 25% (25% x 4 = 100%).
In this case:
- The first quartile is called the 25th percentile as 25% of the data lies beneath this number (represented by 1 in the Google Sheets formula)
- The second quartile or median is the 50th percentile as 50% of the data is below this point (2 in the syntax formula)
- The third quartile is the 75th percentile as 75% of the data lies beneath this point (3 in the Sheets syntax)
After reading this article, you should now know how to find quartiles in Google Sheets. Let us know in the comments if you have any questions and check out our other articles to become a spreadsheet master!