How to rank values (highest = 1) with a formula
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Assign 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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 3 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run Excel.
Functions used
RANK — see full Excel, Google Sheets & LibreOffice compatibility for each.