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.
Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked
Availability and version history
- Introduced: Documented by Apache OpenOffice 4.1.16; original introduction not established.
- Availability: Supported in Apache OpenOffice 4.1.16, verified against its function registry. This identifies a supported release, not a final working version.
- Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
- Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
The RANK function in OpenOffice Calc returns the position of a number within a dataset. It is essential for leaderboards, scoring systems, performance analysis, and any scenario where values must be ordered from highest to lowest or lowest to highest.
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)
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