In Excel, the QUARTILE.EXC and QUARTILE.INC functions are used to calculate specific quartiles for a dataset. Quartiles divide data into four equal parts, with the 1st quartile (Q1) being the 25th percentile, the 2nd quartile (Q2) being the median or 50th percentile, and the 3rd quartile (Q3) being the 75th percentile. The difference between these two functions lies in the range of data they include.
- QUARTILE.EXC: Excludes the 0th and 100th percentiles, meaning it calculates quartiles within the range of 0 to 1, excluding the minimum and maximum values.
- QUARTILE.INC: Includes the 0th and 100th percentiles, meaning it calculates quartiles from the full range of data, including the minimum and maximum values.
Syntax
| Function | Syntax | Description |
|---|---|---|
| QUARTILE.EXC | =QUARTILE.EXC(array, quart) | Returns the specified quartile, excluding the 0th and 100th percentiles. |
| QUARTILE.INC | =QUARTILE.INC(array, quart) | Returns the specified quartile, including the 0th and 100th percentiles. |
Example
Let's say you have the test scores of 10 students and you want to calculate the 1st quartile (25th percentile), 2nd quartile (50th percentile), and 3rd quartile (75th percentile) using both QUARTILE.EXC and QUARTILE.INC.
| Student | Score |
|---|---|
| Student 1 | 72 |
| Student 2 | 88 |
| Student 3 | 91 |
| Student 4 | 65 |
| Student 5 | 85 |
| Student 6 | 78 |
| Student 7 | 92 |
| Student 8 | 70 |
| Student 9 | 95 |
| Student 10 | 82 |
Task: Calculate the 1st, 2nd, and 3rd quartiles for the scores using both QUARTILE.EXC and QUARTILE.INC.
| Quartile | Function | Formula | Explanation |
|---|---|---|---|
| 1st (Q1) | QUARTILE.EXC | =QUARTILE.EXC(B2:B11, 1) | Calculates the 25th percentile, excluding the extremes. |
| 2nd (Q2) | QUARTILE.EXC | =QUARTILE.EXC(B2:B11, 2) | Calculates the 50th percentile (median), excluding the extremes. |
| 3rd (Q3) | QUARTILE.EXC | =QUARTILE.EXC(B2:B11, 3) | Calculates the 75th percentile, excluding the extremes. |
| 1st (Q1) | QUARTILE.INC | =QUARTILE.INC(B2:B11, 1) | Calculates the 25th percentile, including the extremes. |
| 2nd (Q2) | QUARTILE.INC | =QUARTILE.INC(B2:B11, 2) | Calculates the 50th percentile (median), including the extremes. |
| 3rd (Q3) | QUARTILE.INC | =QUARTILE.INC(B2:B11, 3) | Calculates the 75th percentile, including the extremes. |
Practice Exercise 1
In this exercise, you have a dataset representing the weekly production output of a factory over 12 weeks. Calculate the 1st, 2nd, and 3rd quartiles using the QUARTILE.EXC and QUARTILE.INC functions.
Task: Use the data below to calculate the 1st, 2nd, and 3rd quartiles.
Practice Exercise 2
In this exercise, you have data representing the annual bonuses (in dollars) received by employees in a department. Calculate the 1st, 2nd, and 3rd quartiles using the QUARTILE.EXC and QUARTILE.INC functions.
Task: Use the data below to calculate the 1st, 2nd, and 3rd quartiles.
Solutions
Below are the formulas and explanations for each of the tasks provided in the exercises.
Solution for Exercise 1
For the weekly production output data, the following formulas will calculate the quartiles:
| Quartile | Function | Formula | Explanation |
|---|---|---|---|
| 1st (Q1) | QUARTILE.EXC | =QUARTILE.EXC(B2:B13, 1) | Calculates the 25th percentile, excluding the extremes. |
| 2nd (Q2) | QUARTILE.EXC | =QUARTILE.EXC(B2:B13, 2) | Calculates the 50th percentile (median), excluding the extremes. |
| 3rd (Q3) | QUARTILE.EXC | =QUARTILE.EXC(B2:B13, 3) | Calculates the 75th percentile, excluding the extremes. |
| 1st (Q1) | QUARTILE.INC | =QUARTILE.INC(B2:B13, 1) | Calculates the 25th percentile, including the extremes. |
| 2nd (Q2) | QUARTILE.INC | =QUARTILE.INC(B2:B13, 2) | Calculates the 50th percentile (median), including the extremes. |
| 3rd (Q3) | QUARTILE.INC | =QUARTILE.INC(B2:B13, 3) | Calculates the 75th percentile, including the extremes. |
Solution for Practice Exercise 2
For the annual bonuses received by employees, the following formulas will calculate the quartiles:
| Quartile | Function | Formula | Explanation |
|---|---|---|---|
| 1st (Q1) | QUARTILE.EXC | =QUARTILE.EXC(B2:B11, 1) | Calculates the 25th percentile, excluding the extremes. |
| 2nd (Q2) | QUARTILE.EXC | =QUARTILE.EXC(B2:B11, 2) | Calculates the 50th percentile (median), excluding the extremes. |
| 3rd (Q3) | QUARTILE.EXC | =QUARTILE.EXC(B2:B11, 3) | Calculates the 75th percentile, excluding the extremes. |
| 1st (Q1) | QUARTILE.INC | =QUARTILE.INC(B2:B11, 1) | Calculates the 25th percentile, including the extremes. |
| 2nd (Q2) | QUARTILE.INC | =QUARTILE.INC(B2:B11, 2) | Calculates the 50th percentile (median), including the extremes. |
| 3rd (Q3) | QUARTILE.INC | =QUARTILE.INC(B2:B11, 3) | Calculates the 75th percentile, including the extremes. |
These tables provide a clear overview of how to calculate quartiles using both QUARTILE.EXC and QUARTILE.INC functions, with explanations to guide you in applying these formulas in Excel.