Dynamic Sales Commission Calculator

Ecologic , Baddi pays variable commissions based on tiered performance.
Create a dynamic commission calculator that automatically computes bonuses using modern array functions

Using Functions such as LET, MAP, LAMBDA, Solve this Case

Scenario:

A company pays variable commissions based on tiered performance.
Create a dynamic commission calculator that automatically computes bonuses using modern array functions.

Dataset Structure:

• Employee_ID, Name, Region, Monthly_Sales, Target_Achievement (%)
• Commission Tiers: 0-80%: 2%, 80-100%: 5%, 100-120%: 8%, 120%+: 12%

Tasks:

1.Use LET() to define variables for tier thresholds and rates
2.Build a LAMBDA() function that calculates commission based on achievement percentage
3.Apply MAP() to process the entire sales team array without manual dragging
4.Create a spill-range formula that auto-expands when new employees are added

Expected Output:

Dynamic commission table with automatic recalculation and expandable ranges.
Evaluation Criteria: Correct use of LET variables, proper LAMBDA syntax, MAP array processing

Employee_ID Name Region Monthly_Sales Target_Achievement (%)
EMP001 John Smith North 45000 65
EMP002 Sarah Johnson South 52000 82
EMP003 Mike Wilson East 68000 105
EMP004 Emily Brown West 72000 125
EMP005 David Lee North 38000 72
EMP006 Lisa Garcia South 58000 95
EMP007 James Martinez East 75000 118
EMP008 Jennifer Davis West 82000 135
EMP009 Robert Taylor North 42000 78
EMP010 Amanda Anderson South 55000 88
EMP011 Chris Thomas East 65000 102
EMP012 Jessica White West 78000 128
EMP013 Daniel Harris North 48000 85
EMP014 Ashley Clark South 60000 98
EMP015 Matthew Lewis East 70000 115
EMP016 Stephanie Robinson West 85000 142
EMP017 Andrew Walker North 40000 68
EMP018 Nicole Hall South 56000 92
EMP019 Kevin Young East 62000 100
EMP020 Rachel King West 76000 122

Using Functions such as

=LET(
tier_thresholds, {0, 80, 100, 120},
tier_rates, {0.02, 0.05, 0.08, 0.12},
tier_names, {"Bronze", "Silver", "Gold", "Platinum"},

sales_data, D2:D,
achievement_data, E2:E,

calculate_commission, LAMBDA(sale, achieve,
LET(
rate, IF(ISBLANK(achieve), 0,
XLOOKUP(achieve, tier_thresholds, tier_rates, 0.02, -1)),
tier, IF(ISBLANK(achieve), "",
XLOOKUP(achieve, tier_thresholds, tier_names, "Bronze", -1)),
HSTACK(sale rate, rate 100, tier)
)
),

MAP(sales_data, achievement_data, calculate_commission)
)