You have 2 minutes to solve this Excel problem.
You have the following data:
+----------+--------+
| Employee | Sales |
+----------+--------+
| John | 12,000 |
| Sarah | 18,000 |
| Mike | 15,000 |
| David | 20,000 |
| Alice | 10,000 |
+----------+--------+
How would you rank each employee based on their sales, with the highest sales getting Rank 1?
๐ ๐ฒ: Challenge accepted! ๐ช
=RANK(C2,C2:C6,0)
๐ก Explanation:
The RANK() function returns the rank of a number within a list.
โข C2 is the sales value to rank.
โข C2:C6 is the fixed range containing all sales values.
โข 0 ranks values in descending order, so the highest sales receive Rank 1.
โข Copy the formula down to rank all employees.
This challenge tests your understanding of: โ RANK()
โ Relative & Absolute References
โ Ranking Data
โ Excel Formulas
๐ฏ Expected Output Example
+----------+--------+------+
| Employee | Sales | Rank |
+----------+--------+------+
| John | 12,000 | 4 |
| Sarah | 18,000 | 2 |
| Mike | 15,000 | 3 |
| David | 20,000 | 1 |
| Alice | 10,000 | 5 |
+----------+--------+------+
๐ Bonus (Handle Duplicate Rankings)
=RANK.EQ(C2,C2:C6,0)
Or use:
=RANK.AVG(C2,C2:C6,0)
RANK.EQ() assigns the same rank to duplicate values.
RANK.AVG() assigns the average rank to duplicate values.
๐ Be comfortable using:
โข RANK()
โข RANK.EQ()
โข RANK.AVG()
โข LARGE()
โข SMALL()
These functions are frequently used in sales reports, leaderboards, and performance dashboards.
โค๏ธ React with โค๏ธ for more Excel interview challenges!