Browse

RANK Function (Legacy) — LibreOffice Calc

Math Beginner LibreOffice Calc LibreOffice 3.0

The legacy RANK function in LibreOffice Calc returns the rank of a number within a dataset. It behaves like RANK.EQ, assigning the same rank to tied values. This guide explains syntax, examples, tie behavior, errors, and best practices.

The **RANK** function is the legacy version of LibreOffice’s ranking functions. It behaves like **RANK.EQ**, assigning the **same rank** to tied values. Although still supported for compatibility, it has been replaced by **RANK.EQ** and **RANK.AVG** in modern spreadsheets.

This guide provides syntax, examples, tie behavior, advanced techniques, common errors, and best practices, making it a complete reference for the legacy RANK function.

What the RANK Function Does

  • Returns the rank of a number within a dataset
  • Assigns equal ranks to tied values (same behavior as RANK.EQ)
  • Supports ascending or descending ranking
  • Works with numbers, ranges, and references
  • Useful for older spreadsheets and compatibility scenarios
  • Works across sheets

RANK is deprecated but still functional.

Syntax

RANK(number; range; order)

Where:

  • number — the value to rank
  • range — the dataset
  • order — optional
    • 0 or omitted → rank largest to smallest
    • 1 → rank smallest to largest
For new spreadsheets, use RANK.EQ or RANK.AVG instead.

Basic Examples

Rank values from largest to smallest (default)

=RANK(A1; A1:A10)

Rank values from smallest to largest

=RANK(A1; A1:A10; 1)

Rank using a cell reference

=RANK(B5; B1:B100)

Rank across sheets

=RANK(A1; Sheet2.A1:A100)

Tie Behavior (Equal Ranking)

RANK assigns the same rank to tied values.

Example dataset:

90, 85, 85, 70

Descending ranks:

  • 90 → rank 1
  • 85 → rank 2
  • 85 → rank 2
  • 70 → rank 4

Rank 3 is skipped — identical to RANK.EQ.

Advanced Examples

Dynamic ranking for a leaderboard

=RANK(B2; $B$2:$B$50)

Rank with ties broken by a secondary column (manual)

=RANK(A2; A$2:A$50) + COUNTIFS(A$2:A$50; A2; B$2:B$50; ">" & B2)

Rank ignoring zeros

=RANK(A2; IF(A$2:A$50<>0; A$2:A$50); 0)

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

Rank only visible cells (filtered data)

RANK does not ignore hidden rows.
Use AGGREGATE:

=AGGREGATE(14; 1; A1:A100; RANK(A1; A1:A100))

Rank percentiles using RANK + COUNT

=RANK(A1; A$1:A$100) / COUNT(A$1:A$100)

Rank with conditional subset (indirect)

=RANK(A2; IF(B$2:B$50="North"; A$2:A$50))

Common Errors and Fixes

Err:502 — Invalid argument

Occurs when:

  • number is not numeric
  • Range contains no numeric values

Err:504 — Parameter error

Occurs when:

  • Semicolons are incorrect
  • Range is malformed

RANK returns unexpected results

Possible causes:

  • Hidden rows included
  • Zeros included
  • Ties not handled as expected

Fix:
Use RANK.AVG for averaged ranks or RANK.EQ for modern behavior.

RANK differs from RANK.AVG

This is expected—RANK averages nothing; ties share the same rank.

Best Practices

  • Use RANK only for compatibility with older spreadsheets
  • Prefer RANK.EQ for modern ranking
  • Prefer RANK.AVG for statistical ranking
  • Use AGGREGATE for visibility‑aware ranking
  • Use helper columns for tie‑breaking
  • Clean imported data before ranking
RANK is fully supported but no longer recommended — use RANK.EQ or RANK.AVG for new workbooks.