PERCENTRANK.INC Function (LibreOffice Calc)
The PERCENTRANK.INC function in LibreOffice Calc returns the percentile rank of a value within a dataset using the inclusive method. This guide explains syntax, examples, interpolation, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
What the PERCENTRANK.INC Function Does ▾
- Returns the percentile rank of a value
- Uses the inclusive method (0 and 1 allowed)
- Works with numbers, ranges, and arrays
- Supports interpolation between values
- Useful for grading, scoring, and distribution analysis
- Works across sheets
PERCENTRANK.INC is the standard percentile‑rank function in most spreadsheet systems.
Syntax ▾
PERCENTRANK.INC(range; value; significance)
Where:
range— datasetvalue— the number to evaluatesignificance— optional number of decimal places (default = 3)
value does not need to appear in the dataset — interpolation is used.
Basic Examples ▾
Percentile rank of a value
=PERCENTRANK.INC(A1:A100; B1)
Percentile rank with 4 decimal places
=PERCENTRANK.INC(A1:A100; B1; 4)
Percentile rank of the minimum and maximum
=PERCENTRANK.INC(A1:A100; MIN(A1:A100)) → 0
=PERCENTRANK.INC(A1:A100; MAX(A1:A100)) → 1
Percentile rank across sheets
=PERCENTRANK.INC(Sheet1.A1:A100; 75)
Advanced Examples ▾
Percentile rank ignoring errors (using AGGREGATE)
=PERCENTRANK.INC(AGGREGATE(15; 2; A1:A100); B1)
Percentile rank of visible cells only (filtered data)
=PERCENTRANK.INC(FILTER(A1:A100; SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0))); B1)
Conditional percentile rank (indirect)
=PERCENTRANK.INC(IF(B1:B100="North"; A1:A100); 75)
(Confirm with Ctrl+Shift+Enter in older Calc versions.)
Percentile rank excluding zeros
=PERCENTRANK.INC(IF(A1:A100<>0; A1:A100); B1)
Percentile rank for grading
=PERCENTRANK.INC(Scores; StudentScore)
Percentile rank for normalized scoring
=PERCENTRANK.INC(A1:A100; A1)
How PERCENTRANK.INC Calculates Values ▾
- Sorts the dataset
- Finds the position of
value - Computes percentile rank:
rank = (position - 1) / (n - 1) - Interpolates if
valuefalls between two data points
Example:
Dataset size = 11
Value = 75
Position = 8
Rank = (8 - 1) / (11 - 1) = 0.7 → 70th percentile
Differences Between PERCENTRANK.INC and PERCENTRANK.EXC ▾
| Feature | PERCENTRANK.INC | PERCENTRANK.EXC |
|---|---|---|
| Allows 0 | Yes | No |
| Allows 1 | Yes | No |
| Percentile basis | Inclusive | Exclusive |
| Use cases | General statistics | Academic/strict |
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
- Range contains no numeric values
valueis non-numericsignificanceis negative
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range is malformed
Percentile rank returns unexpected result
Possible causes:
- Dataset contains zeros
- Dataset contains errors
- Hidden rows included
- Value outside dataset range
Percentile rank differs from PERCENTRANK.EXC
This is expected — EXC excludes endpoints.
Best Practices ▾
- Use PERCENTRANK.INC for general percentile‑rank calculations
- Use PERCENTRANK.EXC for strict statistical analysis
- Use array formulas for conditional percentile ranks
- Use AGGREGATE for error‑tolerant or visibility‑aware ranking
- Clean imported data before analysis
- Use named ranges for cleaner formulas
PERCENTRANK.INC is ideal for grading curves, performance scoring, and any analysis where inclusive percentile ranks are appropriate.