RANK Function (OpenOffice Calc)
The RANK function in OpenOffice Calc returns the rank of a number within a dataset. Learn syntax, descending vs ascending ranking, tie behavior, examples, 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 list
- Supports descending (default) and ascending ranking
- Handles ties by assigning the same rank
- Works across sheets
- Useful for scoring, grading, and performance analysis
RANK is ideal when you need to determine position within a dataset.
Syntax ▾
RANK(number; range; order)
Arguments:
- number — The value to rank
- range — The dataset to compare against
- order — Optional
0or omitted → rank descending (largest = rank 1)1→ rank ascending (smallest = rank 1)
Ties receive the same rank, and subsequent ranks are skipped.
Basic Examples ▾
Rank a value in descending order (default)
=RANK(A1; A1:A10)
If A1 is the largest value, result = 1.
Rank a value in ascending order
=RANK(A1; A1:A10; 1)
If A1 is the smallest value, result = 1.
Rank using explicit order
=RANK(A1; A1:A10; 0)
Descending.
Tie Behavior Example ▾
Values: 90, 80, 80, 70
Descending ranks:
- 90 → rank 1
- 80 → rank 2
- 80 → rank 2
- 70 → rank 4 (rank 3 is skipped)
Advanced Examples ▾
Rank across sheets
=RANK(Sheet1.A1; Sheet1.A1:A100)
Dynamic rank in a table
=RANK(A2; $A$2:$A$100)
Rank with multiple criteria (workaround)
Rank by score, then by name alphabetically:
=RANK(A2; $A$2:$A$100) + (COUNTIF($A$2:A2; A2)-1) * 0.0001
Rank only visible (filtered) rows
=RANK(A2; IF(SUBTOTAL(103; OFFSET(A2; ROW(A2:A100)-ROW(A2); 0)); A2:A100))
Confirm with Ctrl+Shift+Enter.
Rank within a category (conditional rank)
=RANK(A2; IF(B$2:B$100=B2; A$2:A$100))
Confirm with Ctrl+Shift+Enter.
Rank percent (percentile-style)
=RANK(A1; A1:A10) / COUNT(A1:A10)
Rank with duplicates resolved by secondary key
=RANK(A2; $A$2:$A$100) + (ROW() / 100000)
Common Errors and Fixes ▾
RANK returns Err:502 (Invalid argument)
Occurs when:
- Range contains no numeric values
- Number is text
- Range is malformed
RANK returns unexpected results
Possible causes:
- Wrong ranking order (ascending vs descending)
- Text numbers not converted to numeric
- Hidden duplicates
RANK ignores values you expected it to include
RANK ignores:
- Text numbers (
"123") - Empty cells
- Logical values
- Errors
RANK includes values you expected it to ignore
RANK includes:
- Dates
- Times
- Numeric results of formulas
Err:508 — Missing parenthesis
Usually caused by:
- Missing
) - Using commas instead of semicolons
Best Practices ▾
- Use descending order for leaderboards
- Use ascending order for ranking smallest values
- Use array formulas for conditional ranking
- Convert imported text numbers to real numbers
- Use named ranges for cleaner formulas
- Resolve ties with secondary criteria when needed
For modern spreadsheets, RANK.EQ and RANK.AVG offer more control — but OpenOffice Calc uses the classic RANK function.