RANK Function (Legacy) — LibreOffice Calc
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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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 rankrange— the datasetorder— 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:
numberis 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.