LARGE and SMALL in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to identify the top 3 highest and bottom 3 lowest sales amounts from last month to understand sales performance extremes.

📊 Data: You have a list of sales transactions with columns for Transaction ID, Salesperson, and Sales Amount.

🎯 Deliverable: Create a summary table showing the top 3 largest sales amounts and the bottom 3 smallest sales amounts.

Sample Data

Transaction ID	Salesperson	Sales Amount
1001	Alice	250
1002	Bob	450
1003	Charlie	150
1004	Diana	700
1005	Eva	300
1006	Frank	100
1007	Grace	600
1008	Hank	200
1009	Ivy	400
1010	Jack	350

1

Step 1: Create a new table with two columns: 'Top 3 Sales' and 'Bottom 3 Sales'.

No formula needed for this step.

Expected Result

A blank table with headers 'Top 3 Sales' and 'Bottom 3 Sales' ready for formulas.

2

Step 2: In the first cell under 'Top 3 Sales', enter a formula to find the largest sales amount.

=LARGE(C2:C11, 1)

Expected Result

700 (the highest sales amount)

3

Step 3: In the next cell under 'Top 3 Sales', enter a formula to find the second largest sales amount.

=LARGE(C2:C11, 2)

Expected Result

600 (the second highest sales amount)

4

Step 4: In the third cell under 'Top 3 Sales', enter a formula to find the third largest sales amount.

=LARGE(C2:C11, 3)

Expected Result

450 (the third highest sales amount)

5

Step 5: In the first cell under 'Bottom 3 Sales', enter a formula to find the smallest sales amount.

=SMALL(C2:C11, 1)

Expected Result

100 (the lowest sales amount)

6

Step 6: In the next cell under 'Bottom 3 Sales', enter a formula to find the second smallest sales amount.

=SMALL(C2:C11, 2)

Expected Result

150 (the second lowest sales amount)

7

Step 7: In the third cell under 'Bottom 3 Sales', enter a formula to find the third smallest sales amount.

=SMALL(C2:C11, 3)

Expected Result

200 (the third lowest sales amount)

Final Result

Top 3 Sales | Bottom 3 Sales
------------|----------------
700         | 100
600         | 150
450         | 200

✓The highest sale was 700.

✓The lowest sale was 100.

✓Top 3 sales are significantly higher than the bottom 3 sales.

Bonus Challenge

Add the names of the salespersons who made the top 3 and bottom 3 sales next to the amounts.

Show Hint

Use the INDEX and MATCH functions combined with LARGE and SMALL to find the salesperson names.