PERCENTILE.EXC Function (LibreOffice Calc)
The PERCENTILE.EXC function in LibreOffice Calc returns the k-th percentile of a dataset using the exclusive method. This guide explains syntax, examples, edge cases, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✖ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the PERCENTILE.EXC Function Does ▾
- Returns the k‑th percentile of a dataset
- Uses the exclusive method (k must be between 0 and 1, not including endpoints)
- Works with numbers, ranges, and arrays
- Useful for statistical modeling and academic analysis
- Supports interpolation between values
- Works across sheets
PERCENTILE.EXC is more mathematically strict than PERCENTILE.INC.
Syntax ▾
PERCENTILE.EXC(range; k)
k must be greater than 0 and less than 1.Example: 0.25 = 25th percentile.
Basic Examples ▾
90th percentile (exclusive)
=PERCENTILE.EXC(A1:A100; 0.9)
25th percentile (exclusive)
=PERCENTILE.EXC(A1:A100; 0.25)
Median using PERCENTILE.EXC
=PERCENTILE.EXC(A1:A100; 0.5)
Invalid endpoints
=PERCENTILE.EXC(A1:A100; 0) → error
=PERCENTILE.EXC(A1:A100; 1) → error
Advanced Examples ▾
Percentile across sheets
=PERCENTILE.EXC((Sheet1.A1:A100; Sheet2.A1:A100); 0.9)
Percentile ignoring errors (using AGGREGATE)
=AGGREGATE(18; 2; A1:A100; 0.9)
Percentile of visible cells only (filtered data)
=AGGREGATE(18; 1; A1:A100; 0.9)
Conditional percentile (indirect)
=PERCENTILE.EXC(IF(B1:B100="North"; A1:A100); 0.9)
(Confirm with Ctrl+Shift+Enter in older Calc versions.)
Percentile excluding zeros
=PERCENTILE.EXC(IF(A1:A100<>0; A1:A100); 0.9)
Percentile using sorted helper column
=INDEX(SORT(A1:A100); ROUNDUP((COUNT(A1:A100)+1)*0.9; 0))
Percentile for statistical modeling
=PERCENTILE.EXC(Data; 0.95)
How PERCENTILE.EXC Calculates Values ▾
- Sorts the dataset
- Computes position:
position = (n + 1) * k - Interpolates if position is not an integer
- Fails if position < 1 or > n
Example:
Dataset size = 10
k = 0.9
Position = (10 + 1) * 0.9 = 9.9
Result = 90% between 9th and 10th values.
Differences Between PERCENTILE.INC and PERCENTILE.EXC ▾
| Feature | PERCENTILE.INC | PERCENTILE.EXC |
|---|---|---|
| Allows k = 0 | Yes | No |
| Allows k = 1 | Yes | No |
| Formula basis | (n‑1)*k + 1 | (n+1)*k |
| Statistical strictness | Standard | Academic/strict |
| Use cases | Grading, finance | Research, modeling |
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
k≤ 0 ork≥ 1- Range contains no numeric values
- Non-numeric text is included
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range is malformed
Percentile returns unexpected result
Possible causes:
- Dataset contains zeros
- Dataset contains errors
- Hidden rows included
Fix:
Use AGGREGATE for visibility‑aware percentiles.
Percentile differs from PERCENTILE.INC
This is expected—EXC excludes endpoints and uses a different formula.
Best Practices ▾
- Use PERCENTILE.EXC for strict statistical analysis
- Use PERCENTILE.INC for general-purpose percentile calculations
- Use AGGREGATE for error‑tolerant or visibility‑aware percentiles
- Use array formulas for conditional percentiles
- Clean imported data before analysis
- Use named ranges for cleaner formulas
PERCENTILE.EXC is ideal for academic statistics, research, and modeling where endpoints must be excluded.