MAX & MIN: Find Extreme Values (2025)
Quick Answer: MAX finds highest: =MAX(A1:A10). MIN finds lowest: =MIN(A1:A10). Both ignore text/blanks. For conditions use MAXIFS/MINIFS. Work with numbers, dates, times. Simple and fast.
What are MAX and MIN?
MAX and MIN are Excel's functions for finding the highest and lowest values in a range. They're simple, fast, and work with numbers, dates, and times.
MAX
=MAX(A1:A10)
Returns: Highest value
Example: 5, 12, 3, 18, 7 → 18
Use for:
• Highest score
• Peak sales
• Latest date
MIN
=MIN(A1:A10)
Returns: Lowest value
Example: 5, 12, 3, 18, 7 → 3
Use for:
• Lowest price
• Minimum stock
• Earliest date
Quick Example:
Sales Data:
| Month | Sales |
|---|---|
| Jan | $15,000 |
| Feb | $12,500 |
| Mar | $18,200 |
| Apr | $9,800 |
=MAX(B2:B5)
Best month: $18,200
=MIN(B2:B5)
Worst month: $9,800
The MAX/MIN Family:
MAX / MIN
Basic: Find highest/lowest
=MAX(A1:A10)
MAXA / MINA
Includes TRUE/FALSE values
=MAXA(A1:A10)
MAXIFS / MINIFS
With conditions (Excel 2019+)
=MAXIFS(A:A, B:B, "West")
LARGE / SMALL
Nth highest/lowest
=LARGE(A1:A10, 2)
What MAX/MIN Ignore:
- • Text values: "100" stored as text is ignored
- • Blank cells: Empty cells don't affect result
- • Logical values: TRUE/FALSE ignored (use MAXA/MINA to include)
- • Error values: #N/A, #DIV/0! cause function to return error
💡 Common Use Cases
- • Sports scores: Highest/lowest game score
- • Temperature data: Daily high/low temperatures
- • Financial analysis: Peak profit, minimum loss
- • Quality control: Maximum/minimum measurements
- • Date ranges: Most recent/earliest date
- • Inventory: Highest/lowest stock levels
MAX and MIN Syntax
MAX Function
=MAX(number1, [number2], ...)
Returns the largest value from a set of numbers.
number1 (required)
First number, cell reference, or range
A1 or A1:A10 or 100number2, ... (optional)
Additional numbers (up to 255 arguments)
A1:A10, C1:C10, 500MIN Function
=MIN(number1, [number2], ...)
Returns the smallest value from a set of numbers.
number1 (required)
First number, cell reference, or range
A1 or A1:A10 or 10number2, ... (optional)
Additional numbers (up to 255 arguments)
A1:A10, C1:C10, 50Advanced Functions
MAXIFS (Excel 2019/365)
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Find max value meeting multiple conditions
MINIFS (Excel 2019/365)
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Find min value meeting multiple conditions
Common Patterns
Basic Range
=MAX(A1:A100)
Multiple Ranges
=MAX(A:A, C:C, E:E)
With Condition (2019+)
=MAXIFS(Sales, Region, "West", Year, 2025)
Array Formula (Legacy)
=MAX(IF(Region="West", Sales))
Press Ctrl+Shift+Enter
Exclude Zeros
=MIN(IF(A1:A10>0, A1:A10))
Array formula
⚠️ Important Notes
- • Empty ranges: MAX/MIN of empty range returns 0
- • Text numbers: "100" stored as text is ignored
- • Error handling: One error in range causes function to error
- • Dates: MAX finds latest date, MIN finds earliest (dates are numbers)
- • Performance: Very fast even on large ranges (100k+ cells)
- • Alternative names: LARGE(range,1)=MAX, SMALL(range,1)=MIN
12+ MAX & MIN Examples
Basic MAX & MIN
1. Highest Score
=MAX(B2:B10)
Find best test score in range
2. Lowest Price
=MIN(C2:C50)
Find cheapest product
3. Multiple Ranges
=MAX(A1:A10, C1:C10, E1:E10)
Find max across multiple columns
Date Applications
4. Most Recent Date
=MAX(A2:A100)
Latest order date (dates are numbers)
5. Earliest Date
=MIN(A2:A100)
First order date in range
6. Date Range Duration
=MAX(A2:A100) - MIN(A2:A100)
Days between earliest and latest dates
Conditional MAX/MIN (Excel 2019+)
7. Max Sales by Region
=MAXIFS(Sales, Region, "West")
Highest sale in West region only
8. Min Price by Category
=MINIFS(Prices, Category, "Electronics")
Cheapest electronics item
9. Multiple Conditions
=MAXIFS(Sales, Region, "West", Year, 2025, Status, "Complete")
Max with 3 conditions
Advanced Techniques
10. Exclude Zeros
=MIN(IF(A1:A10>0, A1:A10))
Minimum excluding zeros (Ctrl+Shift+Enter for array)
11. Second Highest (LARGE)
=LARGE(A1:A10, 2)
2nd highest value (3rd = LARGE(..., 3))
12. Second Lowest (SMALL)
=SMALL(A1:A10, 2)
2nd lowest value
13. Max with XLOOKUP
=XLOOKUP(MAX(Sales), Sales, Names)
Find name of person with highest sales
🎯 Pro Tips
- • Dates: MAX = latest, MIN = earliest (dates are serial numbers)
- • Times: Works with time values (0.5 = noon)
- • Conditional (old Excel): Array formula =MAX(IF(condition, range))
- • Nth values: Use LARGE(range, n) or SMALL(range, n)
- • With INDEX/MATCH: Find associated value (e.g., name of max sale)
- • Error handling: Use IFERROR if range might contain errors
Frequently Asked Questions
Why does my MAX function return 0?
MAX returns 0 when: (1) All values are negative, (2) Range is empty, (3) Range contains only text/blanks. Check that your range has actual numbers. If cells show numbers but MAX returns 0, they might be stored as text - use VALUE() to convert or check formatting.
How do I find who has the max/min value?
Use INDEX/MATCH or XLOOKUP: =INDEX(Names, MATCH(MAX(Sales), Sales, 0)) returns name of person with max sales. Or Excel 365: =XLOOKUP(MAX(Sales), Sales, Names). This finds the position of max value and returns corresponding name.
Can MAX/MIN ignore errors like #N/A?
No, one error causes MAX/MIN to error. Wrap in IFERROR: =IFERROR(MAX(A1:A10), 0). Or use array formula to skip errors: =MAX(IF(NOT(ISERROR(A1:A10)), A1:A10)) (Ctrl+Shift+Enter). MAXIFS/MINIFS also error on error values.
What's the difference between MAX and MAXA?
MAX: Ignores text and logical values. =MAX(1, 2, TRUE) = 2. MAXA: Treats TRUE=1, FALSE=0, text=0. =MAXA(1, 2, TRUE) = 2. Use MAX for pure numbers, MAXA to include TRUE/FALSE in calculations. Most use cases need MAX.
How do I find max excluding certain values?
Use MAXIFS (2019+): =MAXIFS(Sales, Status, '<>Cancelled'). Or array formula: =MAX(IF(Status<>'Cancelled', Sales)) (Ctrl+Shift+Enter). To exclude zeros: =MAX(IF(A1:A10>0, A1:A10)). MAXIFS is cleaner for multiple conditions.
Can I use MAX/MIN with text?
No. MAX/MIN work with numbers only. Text is ignored. For text, use different approach: Alphabetically last = INDEX(range, MATCH(MAX(COUNTIF(range, '<='&range)), COUNTIF(range, '<='&range), 0)). Complex - usually sort is easier than formula for text extremes.