RANK.AVG Function (LibreOffice Calc)

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

RANK.AVG is the statistical counterpart to RANK.EQ.

RANK.AVG(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

Rank values from largest to smallest (default)

=RANK.AVG(A1; A1:A10)

Rank values from smallest to largest

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

Rank using a cell reference

=RANK.AVG(B5; B1:B100)

Rank across sheets

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

If two or more values are equal, RANK.AVG assigns the average of their rank positions.

Example dataset:

90, 85, 85, 70

Descending ranks:

• 90 → rank 1
• 85 → ranks 2 and 3 → average = 2.5
• 70 → rank 4

This avoids skipped ranks.

Dynamic ranking for a leaderboard

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

Rank with ties broken by a secondary column (manual)

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

Rank ignoring zeros

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

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

Rank percentiles using RANK.AVG + COUNT

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

Rank with conditional subset (indirect)

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

• Use RANK.AVG for statistical analysis where ties should not skip ranks
• Use RANK.EQ for competitions and scoring where ties share the same rank
• Use AGGREGATE for visibility‑aware ranking
• Use helper columns for tie‑breaking
• Clean imported data before ranking
• Use named ranges for cleaner formulas