How to Use the QUARTILE Function in Excel
Today, we’ll explore the QUARTILE function in Excel and Google Sheets. QUARTILE is a statistical function that calculates a specific quartile—either the 25th, 50th, or 75th percentile—of a dataset. This function is especially useful for analyzing data distribution and pinpointing outliers.
How QUARTILE Works
The syntax for the QUARTILE function is identical in both Excel and Google Sheets:
=QUARTILE(array, quart)array: This is the array or range of numerical values for which you are calculating the quartile.quart: This integer determines which quartile to return: 1 for the first quartile (25th percentile), 2 for the median (50th percentile), and 3 for the third quartile (75th percentile).
Examples of Using QUARTILE
Finding Quartiles in a Dataset
Consider a dataset of exam scores located in cells A1:A10. To find the first quartile (25th percentile) of this dataset, use the formula:
=QUARTILE(A1:A10, 1)Finding Outliers Using Quartiles
Quartiles are excellent tools for identifying outliers. First, calculate the interquartile range (IQR) by subtracting the first quartile (Q1) from the third quartile (Q3). Data points that fall below Q1 - 1.5 * IQR or above Q3 + 1.5 * IQR are considered outliers. Here is how you can compute this in Excel or Google Sheets:
| Data |
|---|
| 85 |
| 90 |
| 92 |
| 88 |
| 75 |
| 100 |
| 110 |
=QUARTILE(A1:A7, 1) //First Quartile (Q1)=QUARTILE(A1:A7, 3) //Third Quartile (Q3)=QUARTILE(A1:A7, 3) - QUARTILE(A1:A7, 1) //Interquartile Range (IQR)=QUARTILE(A1:A7, 1) - 1.5 * (QUARTILE(A1:A7, 3) - QUARTILE(A1:A7, 1)) //Lower Bound for Outliers=QUARTILE(A1:A7, 3) + 1.5 * (QUARTILE(A1:A7, 3) - QUARTILE(A1:A7, 1)) //Upper Bound for OutliersUsing this method, you can effectively identify any potential outliers in your dataset based on the IQR rule.
That concludes our overview of the QUARTILE function! It’s an effective analytical tool that offers deep insights into the distribution of your data.
More information: https://support.microsoft.com/en-us/office/quartile-function-93cf8f62-60cd-4fdb-8a92-8451041e1a2a