How to use MIN function in Excel

This tutorial demonstrates the versatile Microsoft Excel MIN function (versions 2007-2019), showcasing its use in finding the smallest value, handling conditional minimums, and highlighting the lowest number within a dataset. We'll also troubleshoot common errors.

Excel's MIN Function: Syntax and Examples

The MIN function identifies the smallest numerical value within a specified range. Its syntax is straightforward: MIN(number1, [number2], ...) where number1 is required, and subsequent numbers are optional. Up to 255 arguments are allowed, including numbers, cell references, arrays, and ranges. Text, logical values, and empty cells are ignored.

Example Scenarios:

• Finding the Smallest Value: Given a column of inventory quantities, the formula =MIN(B2:B8) (or =MIN(366, 476, 398, 982, 354, 534, 408)) directly returns the lowest quantity. Using a named range (Qty-in-stock) simplifies the formula to =MIN(Qty-in-stock).

• Identifying the Earliest Date: Similarly, finding the earliest date in a range uses =MIN(B2:B8) (or a named range like Delivery-date).

• Determining the Absolute Minimum: To find the smallest absolute value (ignoring negative signs), combine MIN and ABS: {=MIN(ABS(A1:E12))}, entered as an array formula (Ctrl Shift Enter).

Finding the Minimum Excluding Zeros

To find the smallest non-zero value, use an array formula combining MIN and IF: {=MIN(IF(B2:B15>0,B2:B15))}, again entered with Ctrl Shift Enter.

Conditional Minimums

Finding the minimum based on criteria (e.g., lowest sales for a specific fruit) requires MIN and IF together in an array formula: {=MIN(IF(A2:A15=D2,B2:B15))}, entered with Ctrl Shift Enter.

Highlighting the Smallest Number

Visually identifying the minimum value is easily achieved using Excel's conditional formatting:

1. Go to Conditional Formatting > New Rule.
2. Select "Format only top or bottom ranked values".
3. Choose "Bottom" and set the rank to 1.

To exclude zeros from this highlighting, use a custom formula in conditional formatting: =B2=MIN(IF($B$2:$B$15>0,$B$2:$B$15)).

Troubleshooting MIN Function Errors

• #VALUE!: Indicates an invalid argument, often a cell containing an error or a typo in a cell reference.
• #NUM!: Occurs when the calculated value is outside Excel's numerical limits.
• #DIV/0!: Results from division by zero.
• #NAME?: Usually caused by typos in function names or named ranges.

This comprehensive guide provides various methods for utilizing the MIN function in Excel, covering basic usage, conditional applications, and error handling. Remember to use array formulas (Ctrl Shift Enter) where curly braces {} are indicated.

The above is the detailed content of How to use MIN function in Excel. For more information, please follow other related articles on the PHP Chinese website!