Browse

PERCENTRANK.INC Function (LibreOffice Calc)

Math Intermediate LibreOffice Calc LibreOffice 4.0

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.

The **PERCENTRANK.INC** function returns the **percentile rank** of a value within a dataset using the **inclusive** method. It tells you the relative standing of a value compared to the rest of the data.

This guide provides syntax, examples, interpolation rules, advanced techniques, common errors, and best practices, making it one of the most complete PERCENTRANK.INC references available for LibreOffice Calc.

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 — 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.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

  1. Sorts the dataset
  2. Finds the position of value
  3. Computes percentile rank:
    rank = (position - 1) / (n - 1)
  4. Interpolates if value falls 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
  • 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 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.