Challenge Scenario
In sales operations and incentive compensation, executive leadership awards annual President Club bonuses exclusively to the Top 3 highest revenue producers in the sales division. Rather than manually sorting and re-copying data every month, the threshold needs to update dynamically as revenues change.
As a Sales Operations Analyst, you must evaluate the team's annual bookings:
- Top 3 Cutoff (B9): Determine the exact 3rd highest revenue figure using the
LARGE function.
- Sales Tier (Column C): If a representative's revenue is greater than or equal to the Top 3 Cutoff, award them
"PLATINUM", otherwise assign "STANDARD".
- Quota Achieved (Column D): Mark reps who crossed the baseline $70,000 threshold as
"YES", otherwise "NO".
- Total Platinum Reps (C9): Count qualifying Platinum awardees with
COUNTIF.
Your Sheet Layout
Before entering your sales performance formulas, take a moment to understand the sheet architecture. Separating individual sales revenue records from dynamic ranking thresholds, quota benchmarks, and executive award counts ensures a transparent incentive model.
The sheet is organized into three distinct operational zones:
- Sales Team Ledger Zone (Columns A & B): Contains sales representative names and their respective annual booked revenue figures.
- Performance Tiering Zone (Columns C & D): Determines whether each rep qualifies for the top 3 "PLATINUM" award tier using dynamic rank cutoffs, and verifies baseline quota achievement ($70,000).
- Executive Awards Summary (Rows 8–9): Dynamically calculates the exact 3rd place revenue cutoff using LARGE and tallies the total number of Platinum awardees.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B6 |
Sales Rep, Annual Revenue ($) |
Data (Sales Roster) |
Annual revenue bookings |
Read-only individual representative sales performance records. |
B9 |
Top 3 Cutoff ($) |
Summary (Dynamic Rank Threshold) |
=LARGE(B2:B6, 3) |
Find the 3rd highest annual revenue figure across the team. |
C2:C6 |
Sales Tier |
Category |
=IF(B2 >= $B$9, "PLATINUM", "STANDARD") |
Award "PLATINUM" to reps meeting or exceeding the 3rd place cutoff. |
D2:D6 |
Quota Achieved |
Benchmark Zone |
=IF(B2 >= 70000, "YES", "NO") |
Mark "YES" for reps achieving the $70,000 baseline sales quota. |
C9 |
Total Platinum Reps |
Summary (Award Count) |
=COUNTIF(C2:C6, "PLATINUM") |
Count total representatives who earned the Platinum tier badge. |
This layout ensures that if sales figures are updated, the 3rd place cutoff and tier badges automatically adjust without manual re-sorting.
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cell B9, find the 3rd highest revenue using LARGE(B2:B6, 3).
2
In Column C (C2:C6), assign "PLATINUM" if revenue >= $B$9, otherwise "STANDARD".
3
In Column D (D2:D6), assign "YES" if revenue >= 70000, otherwise "NO".
4
In cell C9, count total "PLATINUM" representatives using COUNTIF.
Solution & Formula Breakdown
Calculating dynamic rank-based awards without manual sorting keeps incentive compensation automated and error-free. Let's see how LARGE and IF work together.
1. Finding the 3rd Place Cutoff with LARGE (B9)
=LARGE(B2:B6, 3)
The LARGE(array, k) function finds the k-th largest value in a range without needing to sort the table:
- 1st highest: $110,000 (Ellen Ripley)
- 2nd highest: $95,000 (Sarah Connor)
- 3rd highest: $88,000 (Dutch Schaefer)
- Cell B9 evaluates to $88,000, setting the dynamic qualification line.
2. Awarding Platinum Status (C2:C6)
=IF(B2 >= $B$9, "PLATINUM", "STANDARD")
Comparing each rep's revenue in B2 against the locked cutoff $B$9 assigns "PLATINUM" to Sarah ($95k), Ellen ($110k), and Dutch ($88k).
3. Baseline Quota Verification (D2:D6)
=IF(B2 >= 70000, "YES", "NO")
Reps earning $70,000 or more receive "YES", while Kyle Reese ($64k) receives "NO".
4. Platinum Tally (C9)
=COUNTIF(C2:C6, "PLATINUM")
Tallies the total number of Platinum award winners: 3.
Remember to lock cell $B$9 when referencing the cutoff threshold. If you omit the dollar signs (B9), dragging the formula down will compare rows against empty cells like B10 and B11!