Browse

RANK.EQ Function (LibreOffice Calc)

Math Beginner LibreOffice Calc LibreOffice 4.0

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

The **RANK.EQ** function returns the rank of a number within a dataset. It assigns the **same rank to tied values**, making it ideal for leaderboards, scoring systems, and statistical analysis.

This guide provides syntax, examples, tie behavior, advanced techniques, common errors, and best practices, making it one of the most complete RANK.EQ references available for LibreOffice Calc.

What the RANK.EQ Function Does

  • Returns the rank of a number within a dataset
  • Assigns equal ranks to tied values
  • Supports ascending or descending ranking
  • Works with numbers, ranges, and references
  • Useful for scoring, competitions, and statistical analysis
  • Works across sheets

RANK.EQ is the modern replacement for the older RANK function.

Syntax

RANK.EQ(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
LibreOffice Calc uses semicolons (;) to separate arguments.

Basic Examples

Rank values from largest to smallest (default)

=RANK.EQ(A1; A1:A10)

Rank values from smallest to largest

=RANK.EQ(A1; A1:A10; 1)

Rank a value using a cell reference

=RANK.EQ(B5; B1:B100)

Rank across sheets

=RANK.EQ(A1; Sheet2.A1:A100)

Tie Behavior

If two or more values are equal, they receive the same rank.

Example dataset:

90, 85, 85, 70

Ranks (descending):

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

Notice that rank 3 is skipped.

Advanced Examples

Dynamic ranking for a leaderboard

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

Rank with ties broken by a secondary column (manual)

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

Rank ignoring zeros

=RANK.EQ(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.EQ does not ignore hidden rows.
Use AGGREGATE:

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

Rank percentiles using RANK.EQ + COUNT

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

Rank with conditional subset (indirect)

=RANK.EQ(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.EQ returns unexpected results

Possible causes:

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

Fix:
Use RANK.AVG for averaged ranks or add tie‑breaking logic.

RANK.EQ differs from RANK.AVG

This is expected—RANK.AVG averages tied ranks.

Best Practices

  • Use RANK.EQ for competitions and scoring where ties share the same rank
  • Use RANK.AVG when ties should receive averaged ranks
  • Use AGGREGATE for visibility‑aware ranking
  • Use helper columns for tie‑breaking
  • Clean imported data before ranking
  • Use named ranges for cleaner formulas
RANK.EQ is perfect for leaderboards, scoring systems, and any scenario where tied values should share the same rank.