In this how to use the Quartile Function in Google Sheets tutorial article, first I’ll talk about the benefit of the Quartile Function, the syntax of the function. After that I’ll explain with example and show you how to use it. And I’ll finish with some FAQs on the Quartile Function.
So read on.
If you need to calculate the quartile for your Google Sheet dataset and you want to find the quartiles manually, the data must be ordered from lowest to highest.
But if you use the quartile function, you will be able to calculate the quartile for the Google Sheet dataset without having to order data from lowest to highest.
Quartiles are actually dictated three points in an array or a dataset. They are:
- In the first quartiles select the middle data point.
- The second quartiles are divided into min and middle data points.
- The third quartile is split into a middle and max data point.
Inside the quartile function, there are 5 quartile numbers, that number (minimum, maximum, median, and two quartile points) is 0, 1,2,3,4.
Of the five quartile number 0 return the minimum value and there also use the Min function. 4 return the maximum value there also use the Max function.
The Google Sheets Quartile function uses the lowest (Min value) and highest (Max value) numbers in the dataset.
Syntax of the Quartile Function for Google Sheets:
The function will return the value closest to a certain quartile in your dataset.
The syntax used QUARTILE function is:
Data: It is an array or range that contains the dataset for the calculation and is used to find the quartile.
quartile_number: this formula tells which quartile value (0 to 4) returns.
- 0 return minimum value in the array or dataset (similar to the MIN function). (“0th percentile”)
- 1 return the value nearest the first quartile number in the array. (“25th percentile”)
- 2 return median value in the array. (“50th percentile”)
- 3 return the value nearest the third quartile number in the array. (“75th percentile”)
- 4 return maximum value in the array or dataset (similar to the Max function). (“100th percentile”)
Examples of uses of Quartile function in Google Sheets
Now you know the basic syntax of the quartile function, so let’s see an example using Quartile Function in a spreadsheet.
In this example, you can see I have used some dataset value A2:A7 and applied quartile function formula =Quartile(data, quartile_number) that has been presented quartile number 0 to 4 and each result in a different quartile number.
How to use the QUARTILE function in Google Sheets
- First, I put some datasets in any cell. For example, I have put some datasets in column B from B2 to B7.
- Click the cell where you want the result for example the cell is C2.
- Type on the keyboard =qu and you can see the quartile function.
- Click quartile and manually type(=QUARTILE(B2:B7,0)) or drag over the intended dataset in the array.
- What does result in you want type quartile number (0 to 4) and press quartile number and do bound by the first bracket.
- Then press enter.
In the above example, you can see the quartile function is very easy to use.
- If data is blank, QUARTILE returns the #NUM!. That’s a means error value.
- If quart is not an integer, it is reduced.
- If quart less than or if quart greater than 4, QUARTILE returns the #NUM!
- When quart is equal to 0 (zero), 2, and 4, respectively then MIN, MEDIAN, and MAX return the same value as QUARTILE.
How to Find Quartiles Manually in Google Sheets
Sometimes you find data manually so I have an easy way. You need to follow the steps below:
- Suppose you have following the data or array: 4,2,3,6,8,5,9,7,1
- First data must be shorted and you find the median/middle value.
- So, eliminate the lowest and the height value.
- In the same process now the first quartile is between 1 to 5.
- And the second quartile is between 5 to 9.
Quartile Function FAQs
How is quartile used in real life?
Some companies use the quartiles to benchmark other companies.
For example, the median company pay for a given position is set at the first quartile of the top 20 companies in that region. The quartiles and IQR information are typically used when you create a box-plot of your data set.
What are the differences between QUARTILE.INC and QUARTILE.EXC in Google sheets?
The syntax of Quartile.inc and quartile function is the same. Here 0 to 4 has a total of 5 values. 0 return minimum value and 4 return maximum value.
Quartile.exc function’s syntax is the same here 1 to 3 has a total of 3 values. There is no minimum and maximum value here.
Quartile function is a very easy and simple way to do, so quartile calculations can be done very easily.
I think if you use the function, it will be easier to do quartile calculations faster than manually. Because to manually, data must be sorted, but if you use a function, you can do calculations in both data sorted or unsorted situations.
I hope that after you go through this article properly, you will understand how to find quartiles manually and through the quartile function in Google sheets.
More Related Google Sheets Tutorials on: