The `QUARTILE.EXC` function in Excel is used to calculate quartiles, which divide a data set into four equal parts. This function excludes the lowest and highest data points when calculating the quartiles, making it different from the `QUARTILE.INC` function.
Syntax
QUARTILE.EXC(array, quart)
- `array`: The range of cells containing the data set for which you want to calculate the quartile.
- `quart`: Specifies which quartile you want. This must be an integer between 1 and 3:
- `1` for the first quartile (25th percentile)
- `2` for the second quartile (median or 50th percentile)
- `3` for the third quartile (75th percentile)
Steps to Use `QUARTILE.EXC`
- Prepare Your Data: Make sure your data is organized within a single column or row range.
- Select a Cell for the Result: Click on the cell where you want the quartile result to be displayed.
- Enter the Function:
- Type `=QUARTILE.EXC(` into the formula bar.
- Select your data range (the `array` argument), followed by a comma.
- Enter the quartile number (1, 2, or 3) for the `quart` argument, and close the parenthesis.
- Example: `=QUARTILE.EXC(A1:A10, 1)` if your data is in cells A1 to A10 and you want the first quartile.
- Press Enter: After completing the formula, press Enter to calculate the specified quartile.
Example
Assume you have data in cells `A1:A10`. Here’s how you would use the `QUARTILE.EXC` function to calculate different quartiles:
- First Quartile (25th percentile):
`=QUARTILE.EXC(A1:A10, 1)`
- Second Quartile (Median or 50th percentile):
`=QUARTILE.EXC(A1:A10, 2)`
- Third Quartile (75th percentile):
`=QUARTILE.EXC(A1:A10, 3)`
Important Notes
- The `QUARTILE.EXC` function only works if the dataset is large enough; specifically, the dataset must have at least three values. Otherwise, it will return an error (`#NUM!`).
- The `QUARTILE.EXC` function is available in Excel 2010 and later versions. In earlier versions, you would use `QUARTILE` which defaults to including the minimum and maximum values (`QUARTILE.INC`).
Using `QUARTILE.EXC` is helpful in statistical analysis when you want to assess the spread and central tendency of your data without including outliers.
Unlock Your Potential
Excel
Access
Power BI
