Challenge Scenario
In sales management, tracking individual quota attainment determines quarterly commissions and annual promotions:
- 120% or more (
D2 >= 1.20): Awarded "TOP PERFORMER".
- 100% to 119% (
D2 >= 1.00): Awarded "MET QUOTA".
- Under 100%: Flagged as
"UNDER TARGET".
As a Commercial Sales Director, you are reviewing Q3 team performance:
- Attainment % (Column D): Divide actual closed sales by quarterly target:
=C2/B2.
- Performance Tier (Column E): Classify the rep using nested IF:
=IF(D2>=1.2, "TOP PERFORMER", IF(D2>=1.0, "MET QUOTA", "UNDER TARGET")).
- Total Team Revenue (Cell C7): Sum actual sales closed across all reps using
SUM(C2:C5).
- Top Performers Count (Cell E7): Count reps who achieved top tier status using
COUNTIF(E2:E5, "TOP PERFORMER").
Your Sheet Layout
Understanding the sales quota leaderboard:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Sales Rep, Target, Actual |
Performance Data |
Rep names, assigned targets, and closed revenues |
Read-only sales results. |
D2:D5 |
Attainment % |
Achievement Zone |
=C2/B2 |
Calculates quota fulfillment percentage. |
E2:E5 |
Performance Tier |
Ranking Zone |
=IF(D2>=1.2, "TOP PERFORMER", IF(D2>=1.0, "MET QUOTA", "UNDER TARGET")) |
Assigns performance badge. |
C7 |
Total Sales |
Summary |
=SUM(C2:C5) |
Combined closed revenue ($207,000.00). |
E7 |
Top Performers |
Summary |
=COUNTIF(E2:E5, "TOP PERFORMER") |
Number of reps exceeding 120% target (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells D2:D5, calculate quota attainment % (= Actual Sales / Target).
2
In cells E2:E5, assign tier ("TOP PERFORMER" for >=1.2, "MET QUOTA" for >=1.0, otherwise "UNDER TARGET").
3
In cell C7, calculate total team revenue using SUM.
4
In cell E7, count the number of reps who earned "TOP PERFORMER" status.
Solution & Formula Breakdown
Let's evaluate sales achievement and badge assignment:
1. Quota Attainment Ratio (D2:D5)
=C2/B2
- Rachel Vance: $62,000 / $50,000 = 124.0%.
- David Kim: $41,000 / $40,000 = 102.5%.
- Marcus Brody: $48,000 / $60,000 = 80.0%.
- Elena Rostova: $56,000 / $45,000 = 124.4%.
2. Badge Classification with Nested IF (E2:E5)
=IF(D2>=1.2, "TOP PERFORMER", IF(D2>=1.0, "MET QUOTA", "UNDER TARGET"))
- Rachel (124%): >= 1.2 → TOP PERFORMER.
- David (102.5%): >= 1.0 → MET QUOTA.
- Marcus (80%): < 1.0 → UNDER TARGET.
- Elena (124.4%): >= 1.2 → TOP PERFORMER.
3. Team Aggregate Metrics (C7 & E7)
- Cell C7 (Total Revenue):
=SUM(C2:C5) → $207,000.00.
- Cell E7 (Top Performers Count):
=COUNTIF(E2:E5, "TOP PERFORMER") → 2 reps.
When formatting percentage rates in Excel, 1.20 represents 120% and 1.00 represents 100%. Make sure your comparison values match this decimal format!