Excel MIN/MAX Functions

last modified October 5, 2026

The MIN and MAX functions are essential statistical functions in Excel. MIN finds the smallest value in a range, while MAX finds the largest. This tutorial provides a comprehensive guide to using these functions with detailed examples. You'll learn basic syntax, practical applications, and advanced techniques to master these functions.

MIN/MAX Function Basics

The MIN function returns the smallest numeric value in a set of values, while MAX returns the largest. They can handle individual numbers, cell references, ranges, or a mix of all three.

Component	Description
Function Names	MIN, MAX
Syntax	=MIN(number1, [number2], ...) =MAX(number1, [number2], ...)
Arguments	1-255 numbers, references, or ranges to evaluate
Return Value	Smallest or largest value in arguments

This table breaks down the essential components of the MIN and MAX functions. It shows the function names, basic syntax format, argument limits, and return value characteristics.

Argument	Meaning
number1	Required. The first number, cell reference, or range to evaluate.
number2, ...	Optional. Additional numbers, cell references, or ranges, up to a total of 255 arguments.

You can mix literals, cell references, and ranges in the same call. Inside a range or reference, MIN and MAX ignore empty cells, text, and logical values, but they include cells that hold the number zero. Logical values and text that look like numbers behave differently when you type them directly into the argument list; that case is covered below.

Basic MIN/MAX Example

This example demonstrates the simplest use of MIN and MAX with individual numbers.

=MIN(5, 10, 15)
=MAX(5, 10, 15)
=MIN(-1, -5, -3)
=MAX(-1, -5, -3)

The first formula finds the smallest number (5) and the second finds the largest (15). The last two formulas work with negatives: MIN returns -5 and MAX returns -1. This shows how MIN/MAX work with hard-coded values without cell references.

MIN/MAX with Cell References

A more practical use involves finding min/max values from specific cells. Here's an example with cell references.

A	B	C
10
20
30
	=MIN(A1:A3)	=MAX(A1:A3)

The table shows a simple spreadsheet with values in column A. Cell B4 contains the MIN formula, and C4 contains the MAX formula.

=MIN(A1:A3)
=MAX(A1:A3)

These formulas find the smallest (10) and largest (30) values in cells A1:A3. Using ranges makes MIN/MAX powerful for analyzing data in spreadsheets.

MIN/MAX with Text and Blank Cells

Inside a range, MIN/MAX automatically ignore text values and empty cells. They do not treat a blank as zero. This example shows how they handle mixed content.

A	B	C
15
Text
25
	=MIN(A1:A4)	=MAX(A1:A4)

The table contains numbers, text, and blank cells to demonstrate how MIN/MAX handle different data types. The text in A2 and the empty A3 are both ignored.

=MIN(A1:A4)
=MAX(A1:A4)

The MIN formula returns 15 and the MAX formula returns 25. Both formulas see only the two numeric cells, A1 and A4, because text and blanks are skipped. This behavior makes MIN/MAX robust for real-world data.

MIN/MAX with Logical Values

Logical values behave differently depending on how they reach the function. Inside a range or reference, MIN and MAX ignore TRUE and FALSE entirely. When you type logical values directly into the argument list, however, Excel counts them, with TRUE as 1 and FALSE as 0. The following example contrasts the two cases.

A	B
5
TRUE
FALSE
	=MIN(A1:A3)
	=MAX(A1:A3)

The table mixes a number with logical values. The range formulas ignore the logical values, so both see only the number 5.

=MIN(A1:A3)                ' 5, TRUE and FALSE are ignored
=MAX(A1:A3)                ' 5, TRUE and FALSE are ignored
=MIN(5, TRUE, FALSE)       ' 0, FALSE counts as 0
=MAX(5, TRUE, FALSE)       ' 5, TRUE counts as 1

The range formulas return 5 because the logical values are skipped. The direct formulas behave differently: MIN(5, TRUE, FALSE) returns 0, since FALSE becomes 0, while MAX still returns 5. To make logical values count even inside a range, use MINA or MAXA.

MIN vs MINA and MAX vs MAXA

The MINA and MAXA functions are the include-everything counterparts of MIN and MAX. They do not skip logical values or text inside a range: TRUE counts as 1, while FALSE and text count as 0. Empty cells are still ignored. Use the plain functions when you want only numeric cells, and the A variants when the presence of a value itself matters.

A	B
5
TRUE
FALSE
	=MIN(A1:A3)
	=MINA(A1:A3)
	=MAXA(A1:A3)

For the same range, MIN returns 5 because it ignores the logical values. MINA returns 0 because it counts FALSE as 0, and MAXA returns 5.

=MIN(A1:A3)    ' 5, logicals ignored
=MINA(A1:A3)   ' 0, TRUE=1, FALSE=0
=MAXA(A1:A3)   ' 5, TRUE=1, FALSE=0

If A1:A3 instead held 5, the text "Text", and FALSE, MINA would still return 0 because text evaluates as 0. This is the opposite of MIN, which ignores text altogether.

MIN/MAX with Dates

Excel stores dates as serial numbers, so MIN and MAX work on them exactly as they do on ordinary numbers. MIN returns the earliest date and MAX returns the latest date.

A
2024-01-15
2023-06-01
2024-03-20
=MIN(A1:A3)
=MAX(A1:A3)

=MIN(A1:A3)   ' 2023-06-01, the earliest date
=MAX(A1:A3)   ' 2024-03-20, the latest date

Format the result cells as dates to display them correctly; otherwise Excel may show the underlying serial number instead.

MIN/MAX with Error Values

If any cell contains an error value, MIN/MAX return that error. This example shows this behavior.

A	B	C
10
#N/A
20
	=MIN(A1:A3)	=MAX(A1:A3)

The table demonstrates how MIN/MAX react when encountering error values. The presence of #N/A in A2 causes both functions to return the error.

=MIN(A1:A3)
=MAX(A1:A3)

Both formulas return #N/A because of the error in A2. Wrap the formula in IFERROR to show a fallback instead, or use AGGREGATE, which can ignore error values and still return a result.

MIN/MAX with Named Ranges

MIN/MAX work seamlessly with named ranges, making formulas more readable. This example shows MIN/MAX with a named range.

A	B	C
100
200
300
	=MIN(SalesData)	=MAX(SalesData)

The table assumes cells A1:A3 are named "SalesData". The formulas reference this named range instead of cell addresses, improving clarity.

=MIN(SalesData)
=MAX(SalesData)

These formulas find the smallest (100) and largest (300) values in the named range "SalesData". Named ranges make formulas more understandable and maintainable.

Conditional MIN/MAX with IF

You can find the smallest or largest value that meets a condition by pairing MIN or MAX with IF. The IF produces an array of the matching values and logical FALSE entries, which MIN and MAX then ignore.

A	B
10	Yes
20	No
30	Yes
40	No
50	Yes

=MIN(IF(B2:B6="Yes", A2:A6))   ' 10
=MAX(IF(B2:B6="Yes", A2:A6))   ' 50

Both formulas consider only the rows whose column B equals "Yes", giving the values 10, 30, and 50. In Excel 2019 and earlier these are array formulas and must be confirmed with Ctrl+Shift+Enter. In Microsoft 365 and Excel 2021 they work with a plain Enter.

MINIFS and MAXIFS

Modern Excel provides dedicated functions for conditional extremes. MINIFS and MAXIFS take the value range first, followed by one or more criteria range and criteria pairs, and they need no special array entry.

=MINIFS(A2:A6, B2:B6, "Yes")   ' 10
=MAXIFS(A2:A6, B2:B6, "Yes")   ' 50

The criteria range and the value range must have the same shape and size, or the functions return #VALUE!. You can add further criteria pairs, for example =MINIFS(A2:A6, B2:B6, "Yes", C2:C6, ">0"), to narrow the result.

MIN/MAX of an Empty Range

When the arguments contain no numbers at all, MIN and MAX return 0 rather than an error. This includes an empty range, a range holding only text, or a range holding only logical values.

=MIN(Z1:Z10)   ' 0, no numbers found
=MAX(Z1:Z10)   ' 0, no numbers found

Because zero is a valid result, do not rely on an error to detect a missing numeric range. Test the range with COUNT when you need to know whether any numbers were present.

MIN/MAX with Dynamic Arrays

In modern Excel, MIN/MAX can work with dynamic array formulas. This example demonstrates finding min/max of a filtered range.

=MIN(FILTER(A1:A10, B1:B10="Yes"))
=MAX(FILTER(A1:A10, B1:B10="Yes"))

These formulas find min/max values in A1:A10 where corresponding B cells contain "Yes". The FILTER function creates a dynamic array that MIN/MAX then process. This enables conditional analysis without helper columns.

MIN/MAX Errors and Troubleshooting

Cause	Symptom	Fix
Range contains an error value	The same error is returned	Remove the error, or use AGGREGATE to skip errors
Every referenced cell is empty or text	0	MIN/MAX return 0 when no numbers are found; verify the range holds numbers
Logical values in a range are skipped	Result uses only the numeric cells	Use MINA or MAXA if TRUE and FALSE must count
Numbers stored as text are ignored	Fewer values than expected	Convert the text to numbers, for example with VALUE
Direct logical or numeric-text arguments count	MIN(5, TRUE) returns 0	Pass a range instead of direct arguments to ignore them
Hidden or filtered rows are included	Result includes hidden values	MIN/MAX include hidden rows; use SUBTOTAL or AGGREGATE
MINIFS or MAXIFS ranges differ in size	#VALUE! error	Make the value range and every criteria range the same shape and size

The MIN and MAX functions are essential for finding extremes in Excel. They ignore text, logical values, and empty cells found inside ranges, yet they count logical values and numeric text supplied as direct arguments, and they return 0 when no numbers are present. When logical values or text must count, switch to MINA and MAXA; when the condition is more complex, use MINIFS and MAXIFS or an array formula with IF. Mastering these distinctions will make your minimum and maximum calculations reliable and easy to explain.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.