Browse

RANK.AVG Function (LibreOffice Calc)

Math Beginner LibreOffice Calc LibreOffice 4.0

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

The **RANK.AVG** function returns the rank of a number within a dataset, assigning the **average rank** to tied values. This makes it ideal for statistical analysis where ties should not skip rank positions.

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

What the RANK.AVG Function Does

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

Syntax

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

Basic Examples

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)

Tie Behavior (Average Ranking)

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.

Advanced Examples

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))

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.AVG returns unexpected results

Possible causes:

  • Hidden rows included
  • Zeros included
  • Ties averaged unexpectedly

Fix:
Use RANK.EQ if you want ties to share the same rank.

RANK.AVG differs from RANK.EQ

This is expected—RANK.AVG averages tied ranks, RANK.EQ does not.

Best Practices

  • 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
RANK.AVG is ideal for grading curves, statistical modeling, and any scenario where ties should be smoothed into averaged ranks.