QUARTILE.EXC Function (LibreOffice Calc)
The QUARTILE.EXC function in LibreOffice Calc returns a specified quartile of a dataset using the exclusive method. This guide explains syntax, examples, quartile logic, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✖ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the QUARTILE.EXC Function Does ▾
- Returns quartiles using the exclusive percentile method
- Allows quart values 1–3 only
- Excludes minimum and maximum values
- Useful for academic statistics and strict distribution analysis
- Supports interpolation
- Works across sheets
QUARTILE.EXC is more mathematically strict than QUARTILE.INC.
Syntax ▾
QUARTILE.EXC(range; quart)
Where quart is:
| Quart | Meaning |
|---|---|
| 1 | 25th percentile (Q1) |
| 2 | 50th percentile (Median) |
| 3 | 75th percentile (Q3) |
QUARTILE.EXC is equivalent to:
PERCENTILE.EXC(range; quart/4)
PERCENTILE.EXC(range; quart/4)
Basic Examples ▾
First quartile (Q1)
=QUARTILE.EXC(A1:A100; 1)
Median (Q2)
=QUARTILE.EXC(A1:A100; 2)
Third quartile (Q3)
=QUARTILE.EXC(A1:A100; 3)
Invalid quart values
=QUARTILE.EXC(A1:A100; 0) → error
=QUARTILE.EXC(A1:A100; 4) → error
Advanced Examples ▾
Quartile across sheets
=QUARTILE.EXC((Sheet1.A1:A100; Sheet2.A1:A100); 3)
Quartile ignoring errors (using AGGREGATE)
=AGGREGATE(19; 2; A1:A100; 3)
Quartile of visible cells only (filtered data)
=AGGREGATE(19; 1; A1:A100; 3)
Conditional quartile (indirect)
=QUARTILE.EXC(IF(B1:B100="North"; A1:A100); 1)
(Confirm with Ctrl+Shift+Enter in older Calc versions.)
Quartile excluding zeros
=QUARTILE.EXC(IF(A1:A100<>0; A1:A100); 3)
Quartile using sorted helper column
=INDEX(SORT(A1:A100); ROUNDUP((COUNT(A1:A100)+1)*0.75; 0))
Quartile for strict statistical modeling
=QUARTILE.EXC(Data; 1) → Q1
=QUARTILE.EXC(Data; 2) → Median
=QUARTILE.EXC(Data; 3) → Q3
How QUARTILE.EXC Calculates Values ▾
QUARTILE.EXC uses the exclusive percentile formula:
position = (n + 1) * (quart / 4)
If position is not an integer, interpolation is used.
Example:
Dataset size = 10
Q1 = 0.25
Position = (10 + 1) * 0.25 = 2.75
Result = 75% between 2nd and 3rd values.
Differences Between QUARTILE.INC and QUARTILE.EXC ▾
| Feature | QUARTILE.INC | QUARTILE.EXC |
|---|---|---|
| Quart range | 0–4 | 1–3 only |
| Includes min/max | Yes | No |
| Percentile basis | Inclusive | Exclusive |
| Use cases | General statistics | Academic/strict statistics |
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
quartis outside 1–3- Range contains no numeric values
- Non-numeric text is included
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range is malformed
Quartile returns unexpected result
Possible causes:
- Dataset contains zeros
- Dataset contains errors
- Hidden rows included
Fix:
Use AGGREGATE for visibility‑aware quartiles.
Quartile differs from QUARTILE.INC
This is expected—EXC excludes endpoints and uses a different formula.
Best Practices ▾
- Use QUARTILE.EXC for strict statistical analysis
- Use QUARTILE.INC for general-purpose quartile calculations
- Use AGGREGATE for error‑tolerant or visibility‑aware quartiles
- Use array formulas for conditional quartiles
- Clean imported data before analysis
- Use named ranges for cleaner formulas
QUARTILE.EXC is ideal for academic research, statistical modeling, and any analysis requiring strict exclusion of endpoints.