Syntax
RANKAVG(number, range, [order])
- number—The number, or cell reference, of the value to rank
- range—The cell references, or range, to rank
- order—[optional]Specifies rank order: 0 (default) is descending order, and 1 is ascending order
Sample usage
RANKAVG([Units Sold]1,[Units Sold]:[Units Sold],1)
Usage notes
Here's how RANKAVG works:
- RANKAVG determines the position of a chosen number within a specified range of numbers.
- If multiple numbers in your list are identical, RANKAVG assigns them the average of the ranks they would have received. For example, if two numbers are tied for the 3rd and 4th positions, both receive a rank of 3.5.
- If multiple numbers in your list are identical, RANKAVG assigns them the average of the positions they would have received if the list were sorted in the desired order. For example, if two numbers are tied for the 3rd rank (taking up positions 3 and 4 in the sorted list), both receive a rank of 3.5. Ranks 3 and 4 don’t appear in the ranking.
- In contrast, when using RANKEQ, both numbers would have received a rank of 3, and rank 4 would be skipped because with RANKEQ, duplicates are all assigned the highest value position.
- You can choose to rank in ascending order (smallest number gets rank 1) or descending order (largest number gets rank 1).
Use RANKAVG to identify top performers, prioritize tasks, analyze data, or any other instance in which you need duplicate values to appear in the lower assigned rank.
Examples
Example 1: Ranking task priority
Imagine you have a list of project tasks with a Priority score column (where a higher score means higher priority). You want to display an easy-to-understand rank value in a report, without showing the priority score.
| Task name | Priority score | Rank (High priority = 1) |
|---|---|---|
| Develop feature A | 90 | 1 |
| Fix bug 1 | 85 | 2 |
| Update documentation | 80 | 3.5 |
| Test module B | 80 | 3.5 |
| Prepare report | 75 | 5 |
Formula in the Rank (High priority = 1) column:
=RANKAVG([Priority score]@row, [Priority score]:[Priority score], 0)
Explanation:
- [Priority score]@row: This refers to the priority score for the current row.
- [Priority score]:[Priority score]: This specifies the entire Priority score column as the range to compare against.
- 0: This indicates descending order. The highest priority score receives a rank of 1.
- Result: Update documentation and Test module B both have a priority score of 80. If sorted individually, they would be 3rd and 4th. RANKAVG assigns them the average rank of (3+4)/2 = 3.5.
Example 2: Ranking team member performance
Suppose you're tracking team member performance based on their task completion rate (percentage completed). You want to rank them to identify top performers.
| Team member | Task completion rate | Performance rank | Performance rank (Rounded) |
|---|---|---|---|
| Alice | 0.95 | 1 | 1 |
| Bob | 0.90 | 2.5 | 3 |
| Carol | 0.88 | 4.5 | 5 |
| David | 0.88 | 4.5 | 5 |
| Eve | 0.90 | 2.5 | 3 |
Formula in the Performance rank column:
=RANKAVG([Task completion rate]@row, [Task completion rate]:[Task completion rate], 0)
Explanation:
- [Task completion rate]@row: The completion rate for the current team member.
- [Task completion rate]:[Task completion rate]: The range of all team members' completion rates.
- 0: Descending order, so higher completion rates get a lower rank (closer to 1).
- Result:
- Bob and Eve both have a 0.90 completion rate. If ranked uniquely, they would be 2nd and 3rd, so RANKAGE gives them both a rank of 2.5
- Carol and David both have a 0.88 completion rate. If ranked uniquely, they would be 4th and 5th, since ranks 2 and 3 were used for the tie between Bob and Eve. RANKAVG gives them both a rank of 4.5.
You format the cells in Performance rank (Rounded) to remove decimal values and round the values to a whole number. This assigns the duplicate values to the lower of the two shared positions in the sorted list.
Example 3: Ranking project budget adherence
You're monitoring various projects and have a Budget variance column (where a lower, or more negative, number indicates going over budget by a greater margin). You want to identify the three projects with the budget variance closest to zero with a star (✩), but if two projects tie for the 3rd rank, they shouldn't receive the star.
| Project name | Budget variance | Budget adherence rank | Top 3 adherence |
|---|---|---|---|
| Project X | -$500 | 1 | ✩ |
| Project Y | -$800 | 2 | ✩ |
| Project Z | -$1000 | 3.5 | |
| Project A | -$1000 | 3.5 |
Formula in the Budget adherence rank column:
=RANKAVG([Budget variance]@row, [Budget variance]:[Budget variance], 1)
Explanation:
- [Budget variance]@row: The budget variance for the current project.
- [Budget variance]:[Budget variance]: The range of budget variances for all projects.
- 1: Ascending order. In this case, a higher (less negative or closer to zero) budget variance indicates better adherence, so we want the smallest numbers to rank highest.
- Result: Project Z and Project A both have a budget variance of -$1000. They would be 3rd and 4th, so RANKAVG gives them both a rank of 3.5.
- The formula in Top 3 adherence is =IF([Budget adherence rank]@row<=3, 1, 0) and adds a star if the rank is less than or equal to 3. Since Project Z and A are duplicates, they average to a value greater than 3 and don't receive the star for the top 3. This prevents the Top 3 ranking from exceeding three items.
Still need help?
Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.