How to rank values (highest = 1) with a formula
✓ Verified in LibreOffice 25.8.7.3Assign a rank to each number in a list, e.g. 1 for the largest.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =RANK(A2,$A$2:$A$6) | Descending by default; add ,1 for ascending. RANK.EQ is the modern name. |
| Google Sheets | =RANK(A2,$A$2:$A$6) | Identical. |
| LibreOffice Calc | =RANK(A2,$A$2:$A$6) | Identical. |
How it works
RANK(number, range) returns the position of the value if the range were sorted largest-to-smallest. 30 is the 3rd biggest of 10/20/30/40/50, so its rank is 3. Lock the range with $ when filling down.
Verified, not just documented
We ran =RANK(A2,A2:A6) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. Every formula here is confirmed by actually executing it.