Menu

PERCENTRANK.EXC Function (LibreOffice Calc)

Math Intermediate LibreOffice Calc Introduced in LibreOffice 4.0
statistics data-analysis distribution percentiles ranking exclusive-percentiles

The PERCENTRANK.EXC function in LibreOffice Calc returns the percentile rank of a value within a dataset using the exclusive method. This guide explains syntax, examples, interpolation, errors, and best practices.

Compatibility

▾

What the PERCENTRANK.EXC Function Does ▾

  • Returns the percentile rank of a value
  • Uses the exclusive method (0 and 1 are not allowed)
  • Works with numbers, ranges, and arrays
  • Supports interpolation
  • Useful for academic statistics and distribution modeling
  • Works across sheets

PERCENTRANK.EXC is more mathematically strict than PERCENTRANK.INC.

Syntax ▾ 

PERCENTRANK.EXC(range; value; significance)

Where:

  • range — dataset
  • value — the number to evaluate
  • significance — 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.EXC(A1:A100; B1)

Percentile rank with 4 decimal places

=PERCENTRANK.EXC(A1:A100; B1; 4)

Invalid endpoints

=PERCENTRANK.EXC(A1:A100; MIN(A1:A100))   → error  
=PERCENTRANK.EXC(A1:A100; MAX(A1:A100))   → error

Percentile rank across sheets

=PERCENTRANK.EXC(Sheet1.A1:A100; 75)

Advanced Examples ▾

Percentile rank ignoring errors (using AGGREGATE)

=PERCENTRANK.EXC(AGGREGATE(15; 2; A1:A100); B1)

Percentile rank of visible cells only (filtered data)

=PERCENTRANK.EXC(FILTER(A1:A100; SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0))); B1)

Conditional percentile rank (indirect)

=PERCENTRANK.EXC(IF(B1:B100="North"; A1:A100); 75)

(Confirm with Ctrl+Shift+Enter in older Calc versions.)

Percentile rank excluding zeros

=PERCENTRANK.EXC(IF(A1:A100<>0; A1:A100); B1)

Percentile rank for grading (strict method)

=PERCENTRANK.EXC(Scores; StudentScore)

Percentile rank for normalized scoring

=PERCENTRANK.EXC(A1:A100; A1)

How PERCENTRANK.EXC Calculates Values ▾

  1. Sorts the dataset
  2. Finds the position of value
  3. Computes percentile rank:
    rank = (position) / (n + 1)
  4. Interpolates if value falls between two data points
  5. Returns an error if rank ≤ 0 or ≥ 1

Example:
Dataset size = 10
Value = 75
Position = 8
Rank = 8 / (10 + 1) = 0.7273

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:

  • value equals min or max of dataset
  • Range contains no numeric values
  • value is non-numeric
  • significance is 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 valid percentile range

Percentile rank differs from PERCENTRANK.INC

This is expected — EXC excludes endpoints.

Best Practices ▾

  • Use PERCENTRANK.EXC for strict statistical analysis
  • Use PERCENTRANK.INC for general percentile‑rank calculations
  • 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.EXC is ideal for academic research, statistical modeling, and any analysis requiring strict exclusion of percentile endpoints.

Related Functions

Copyright 2026. All rights reserved.