RANK Function Example: Excel Ranking Explained
- Average speed
- km/h
- Result
- Finish time (min)
- 50 min
- You entered
- Distance (km)
- 10 km
- You entered
This article explains the Excel RANK function with concrete examples, showing how to rank numbers in a list from highest to lowest or lowest to highest. You will learn the syntax, how to use it with absolute and relative references, and how to handle ties, so you can apply it confidently to your own data.
Formula
The RANK function in Excel returns the position of a number within a list of numbers. Its syntax is:
RANK(number, ref, [order])
- number: the value you want to find the rank for.
- ref: the array or range of numbers containing the value. Non-numeric values in the range are ignored.
- order (optional): a number that specifies how to rank. If 0 or omitted, Excel ranks the largest number as rank 1 (descending). If 1, Excel ranks the smallest number as rank 1 (ascending).
For example, if you have the list {10, 20, 30} and you ask for the rank of 20 with order 0, the result is 2 because 20 is the second largest. With order 1, the result is 2 as well because 20 is the second smallest.
Worked Example
Suppose you have a list of test scores in cells A1:A5: 85, 92, 78, 92, 70. You want to rank each score from highest to lowest. In cell B1, you would enter:
=RANK(A1, $A$1:$A$5, 0)
Copy this down to B5. The results are:
- 85 → rank 3 (there are two 92s above it)
- 92 → rank 1 (tied for first)
- 78 → rank 4
- 92 → rank 1 (tied for first)
- 70 → rank 5
Notice that the two 92s both get rank 1, and the next highest score (85) gets rank 3. This is because RANK skips ranks after ties. If you prefer no gaps (1,2,3…), you would use the RANK.EQ (same as RANK) or RANK.AVG (gives average rank for ties) functions in newer versions of Excel.
How to Use RANK in Real Scenarios
RANK is commonly used for:
- Leaderboards for sales, test scores, or sports times.
- Identifying top or bottom performers in a dataset.
- Creating percentile-like groupings when combined with COUNT.
For instance, to rank athletes by race time (lowest time = best), you would use =RANK(C2, $C$2:$C$100, 1) because you want ascending order (smallest number gets rank 1).
When using RANK, always lock the range with absolute references ($A$1:$A$100) so that copying the formula does not shift the range. If you omit the dollar signs, the range moves relative to each row, giving incorrect ranks.
Common Mistakes and How to Avoid Them
- Forgetting the order argument: Default is descending (largest = 1). If you need smallest = 1, you must specify 1.
- Not using absolute references: Always use $A$1:$A$100 in the ref argument to keep the range constant.
- Mixing text and numbers: RANK ignores text, but if your range contains numbers stored as text, they may be ignored. Use the VALUE function or convert to numbers.
- Expecting no gaps after ties: RANK gives the same rank to ties and then skips the next rank. If you need dense ranks (no gaps), use RANK.EQ (same) or a workaround with SUMPRODUCT.
RANK vs. RANK.EQ vs. RANK.AVG
In Excel 2010 and later, RANK has been replaced by RANK.EQ and RANK.AVG, but RANK still works for compatibility. The differences:
- RANK.EQ: identical to RANK – ties get the same rank, and the next rank is skipped.
- RANK.AVG: ties get the average of the ranks they would have occupied. For example, if two values tie for 2nd and 3rd, both get 2.5.
If you need to assign unique ranks to ties (e.g., 1,2,3,4), you can add a tiebreaker column (like a timestamp) or use COUNTIF to adjust: =RANK(A1,$A$1:$A$5,0)+COUNTIF($A$1:A1,A1)-1.
Alternatives to RANK
Depending on your goal, other functions may be more suitable:
- LARGE and SMALL: return the k-th largest or smallest value directly (e.g., =LARGE(range,2) gives the second largest).
- PERCENTRANK: returns the percentile rank of a value (0 to 1).
- COUNTIF: can count how many values are greater or less than a given value, effectively giving a rank.
- Sorting: if you just need the order, sort the data instead of using RANK.
Frequently Asked Questions
How do I rank in ascending order (smallest gets rank 1)?
Use the order argument as 1: =RANK(number, ref, 1). This is useful for times, distances, or costs where lower is better.
Why does my RANK formula return #N/A?
This happens when the number you are trying to rank does not exist in the ref range. Double-check that the value is actually present and that the range is correct.
How can I break ties so each item gets a unique rank?
Add a tiebreaker column with a unique number (like a row number) and use a formula like =RANK(A1,$A$1:$A$10,0)+COUNTIF($A$1:A1,A1)-1. This gives the first occurrence a lower rank.
Does RANK work with decimals and negative numbers?
Yes, RANK works with any numeric values, including decimals and negatives. The order argument determines whether larger or smaller values get rank 1.
What is the difference between RANK and PERCENTRANK?
RANK returns an integer position (1,2,3,…). PERCENTRANK returns a decimal between 0 and 1, representing the relative standing as a percentage. For example, a rank of 2 out of 10 would be a percentile rank of 0.9 (90th percentile).
Can I use RANK on a filtered list?
RANK ignores hidden rows, but it still ranks based on all rows in the range, including hidden ones. If you need to rank only visible cells, you would need a more complex formula using SUBTOTAL or a helper column with a filter function.
Related
Formula used
in t | Finish time (min) | min | 50 | 0 in d | Distance (km) | km | 10 | 0 out pace | Average pace | min/km | t / d | 2 out speed | Average speed | km/h | d / (t / 60) | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.