When you’re working with numbers in Excel — like marks, sales, expenses, or temperatures — sometimes you need to know:
What’s the lowest value?
What’s the highest value?
That’s exactly what Excel’s MIN and MAX functions help you with!
Let’s explore both in detail with 5 easy-to-understand examples.
What Are MIN and MAX?
MIN: Finds the smallest number in a list.
MAX: Finds the largest number in a list.
They are super handy for quickly analyzing data like best/worst performers, highest/lowest expenses, or top/bottom sales.
Syntax
=MIN(number1, [number2], ...)
=MAX(number1, [number2], ...)
Example 1: Find Lowest and Highest Marks
| Subject | Marks |
|---|---|
| Math | 85 |
| Science | 72 |
| English | 91 |
| History | 65 |
Lowest:
=MIN(B2:B5)→ 65Highest:
=MAX(B2:B5) → 91
Example 2: Find Minimum and Maximum Sales
| Month | Sales (₹) |
|---|---|
| Jan | 25000 |
| Feb | 22000 |
| Mar | 31000 |
| Apr | 18000 |
- Minimum Sales:
=MIN(B2:B5) → ₹18000- Maximum Sales:
=MAX(B2:B5) → ₹31000
Note: Great for identifying your best and worst performing months.
Example 3: Comparing Temperatures
| City | Temp (°C) |
|---|---|
| Delhi | 40 |
| Mumbai | 34 |
| Jaipur | 42 |
| Shimla | 18 |
- Coldest City Temp:
=MIN(B2:B5) → 18°C- Hottest City Temp:
=MAX(B2:B5) → 42°C
Example 4: Set Target Based on Max Monthly Sale (Per Customer)
Suppose you have sales data of 5 customers over 4 months. You want to set each customer’s next month’s target as 20% more than their highest sale in any month.
| Customer | Jan (₹) | Feb (₹) | Mar (₹) | Apr (₹) | Target for May (₹) |
|---|---|---|---|---|---|
| Riya | 22000 | 27000 | 31000 | 29000 | =MAX(B2:E2)*1.2 → 37200 |
| Soham | 18000 | 20000 | 25000 | 24000 | =MAX(B3:E3)*1.2 → 30000 |
| Meera | 32000 | 31000 | 33000 | 35000 | =MAX(B4:E4)*1.2 → 42000 |
| Aarav | 15000 | 14000 | 16000 | 15500 | =MAX(B5:E5)*1.2 → 19200 |
| Tanvi | 27000 | 29000 | 31000 | 30000 | =MAX(B6:E6)*1.2 → 37200 |
Formula (in F2):
=MAX(B2:E2)*1.2or =MAX(B2:E2)*120% or =MAX(B2:E2)*(1+20%)
Tip: Drag this formula down for all customers to automatically calculate 20% more than their maximum monthly sale.
Why This Is Useful
This method helps in:
- Setting realistic and achievable sales targets
- Encouraging incremental growth
- Making use of Excel’s MAX function for performance-based planning
Example 5: Limit Salesman Incentive Using MIN
You want to give your sales team an incentive of ₹2.5 per unit sold, but maximum ₹1400 only. This means if someone sells a lot, they’ll still get no more than ₹1400.
Here’s how you can use the MIN function to apply this rule.
| Salesman | Quantity Sold | Incentive Formula | Final Incentive (₹) |
|---|---|---|---|
| Riya | 500 | =MIN(B2*2.5, 1400) | ₹1250 |
| Soham | 600 | =MIN(B3*2.5, 1400) | ₹1400 (capped) |
| Meera | 400 | =MIN(B4*2.5, 1400) | ₹1000 |
| Aarav | 800 | =MIN(B5*2.5, 1400) | ₹1400 (capped) |
| Tanvi | 300 | =MIN(B6*2.5, 1400) | ₹750 |
Formula (in C2):
=MIN(B2*2.5, 1400)
This formula checks:
- If
(Quantity * 2.5)is less than ₹1400, give full amount. - If it exceeds ₹1400, cap it at ₹1400.
Why This Is Useful
This method helps:
- Prevent overpayment
- Automatically handle incentive caps
- Keep your incentive system fair and Excel-powered
Comparison Table: MIN vs MAX
| Feature | MIN Function | MAX Function |
|---|---|---|
| Finds | Smallest value | Largest value |
| Works with Numbers? | ✅ Yes | ✅ Yes |
| Works with Dates? | ✅ Yes (Earliest date) | ✅ Yes (Latest date) |
| Works with Negatives? | ✅ Yes | ✅ Yes |
| Empty Cells | ❌ Skipped | ❌ Skipped |
| Best Use Case | Lowest marks, lowest sales | Highest marks, best performance |
Real-Life Use Cases
- Track highest and lowest sales
- Find top scorer and lowest marks
- Check fastest and slowest delivery
- Compare minimum and maximum temperature
- Review biggest profit and worst loss
Summary
| Function | Purpose | Ignores |
|---|---|---|
| MIN | Returns smallest number | Blank cells |
| MAX | Returns largest number | Blank cells |
What’s Next?
In the next post, we’ll learn about the LOWER, UPPER & PROPER Function.