Excel Formula: Rank Based on Achieved % of Sales not Targeted
In this article, we will explore how to create a rank-based formula in Excel, which calculates the achieved percentage of sales not targeted according to a given table. This formula can be useful for sales managers and analysts to quickly identify the sales performance of each team member and rank them based on their achievements.
Table Structure
The table we will be using has the following columns:
- Salesperson
- Target Sales
- Actual Sales
Where:
- Salesperson: The name of the salesperson.
- Target Sales: The sales target set for the salesperson.
- Actual Sales: The actual sales achieved by the salesperson.
Formula Structure
The formula we will be creating has the following structure:
=RANK.EQ(achieved\_percentage, achieved\_percentage\_range, 1)
Where:
- achieved\_percentage: The percentage of actual sales achieved compared to the target sales.
- achieved\_percentage\_range: The range of achieved percentages for all salespeople.
- 1: Indicates that we want to rank the highest value first.
Calculating Achieved Percentage
To calculate the achieved percentage, we will use the following formula:
=D2/C2
Where:
- D2: The actual sales achieved by the salesperson.
- C2: The sales target set for the salesperson.
Ranking Based on Achieved Percentage
Now that we have the achieved percentage, we can rank the salespeople based on their performance using the following formula:
=RANK.EQ(E2, $E$2:$E$7, 1)
Where:
- E2: The achieved percentage for the first salesperson.
- $E$2:$E$7: The range of achieved percentages for all salespeople.
- 1: Indicates that we want to rank the highest value first.
In this article, we have learned how to create a rank-based formula in Excel that calculates the achieved percentage of sales not targeted according to a given table. This formula can be useful for sales managers and analysts to quickly identify the sales performance of each team member and rank them based on their achievements. The formula structure consists of calculating the achieved percentage and then ranking the salespeople based on their performance.
References
--end article--