Excel MIN Function

What is the MIN Formula in Excel?

The MIN formula in Excel is a fundamental statistical function used to identify the smallest number in a given set of values. At its core, the formula is designed to simplify data analysis by offering a direct way to pinpoint the lowest figure within a range, whether the numbers are located in consecutive cells or scattered throughout a worksheet.

Syntax of MIN Function in Excel

In Excel, the syntax of a function describes its structure and how it should be written to execute correctly. Understanding the syntax is crucial as it ensures the function delivers accurate results.

For the MIN function, the syntax is quite straightforward:

Here's a breakdown of its components:

• =MIN(: This is the start of the MIN function. The equal sign (=) is essential as it signals Excel to perform a calculation.

• number1: This is a mandatory argument. It represents the first number or reference you want to evaluate.

• [number2], ...: These are optional arguments. You can specify additional numbers or ranges separated by commas. Excel allows you to enter up to 255 number arguments for the MIN function.

• ): This closing parenthesis marks the end of the function.

Examples:

1. To find the minimum number between 5, 10, and 15: $=MIN(5, 10, 15)$, which will return 5.
2. To determine the smallest value in the range B1:B5: $=MIN(B1:B5)$.

Arguments of MIN Function in Excel

The arguments of a function are the inputs it requires to compute and return a result. For the MIN function in Excel, the arguments represent the set of numbers or range of cells you wish to evaluate. Let's dive into a detailed understanding of these arguments.

1. number1 (Required):

   • Description: The first number or reference that you wish to evaluate.
   • Type: Number or reference.
   • Example: 5, A1, or B3:B8

2. number2, ... (Optional):

   • Description: Subsequent numbers or references that you want to evaluate. While these are optional, including them allows for a more extensive comparison of values.
   • Type: Number or reference.
   • Excel permits you to input up to 255 arguments for the MIN function, allowing for evaluating a broad range of data.
   • Example: $=MIN(5, 10, A1:A5, B7)$

Usage:

For instance, if you have the numbers 5, 10, and 15 in cells A1, A2, and A3 respectively, you could use $=MIN(A1, A2, A3)$ or $=MIN(A1:A3)`$to determine the smallest number among them, which in this case, is 5.

Return Value of MIN Function in Excel

The return value is the result that a function delivers after processing the given arguments. This is quite straightforward for the MIN function in Excel: it returns the smallest number from the provided set of values or ranges. Let's explore this in depth.

1. Numeric Return:

   • If all provided arguments are numeric or ranges contain numeric values, the MIN function will return the smallest numeric value.
   • Example: Given the range A1 with values 5, 7, 2, 9, 4, =MIN(A1:A5) will return 2.

2. Text and Error Cells:

   • Text values, error cells, and empty cells within the range are ignored.
   • If the range or arguments solely consist of text errors or are empty, the MIN function will return 0.
   • Example: If A1 has the value #VALUE! and A2 contains the text "apple", the formula =MIN(A1:A2) will yield 0.

3. Logical Values:

   • Logical values are considered numeric in Excel. Specifically, TRUE is evaluated as 1 and FALSE as 0.
   • If logical values are present in the specified range or among the arguments, they will be considered in the calculation.
   • Example: If A1 contains the logical value TRUE and A2 has the number 0.5, the formula =MIN(A1:A2) will return 0.5.

4. Empty Arguments:

   • If the MIN function doesn't receive any arguments or is provided with an empty range, it will produce a #VALUE! error.

Examples of MIN Function in Excel

Using the MIN function effectively in Excel often requires going beyond its basic application. Let's explore some of its versatile uses through examples under each subheading.

Basic Example: The MIN Function Returns the Smallest Numeric Value in Supplied Data

Scenario: Let's say cells A1 through A5 contain the following values: 10, 15, 8, 12, 9.

Formula: $=MIN(A1:A5)$

Result: 8

This simple use of the MIN function returns the smallest value, 8, from the given range.

How to Find the Lowest Value Ignoring Zeros

You might need a combination of functions if you have a range with zeros and want to identify the smallest value excluding these zeros.

Scenario: Consider cells B1 through B5 have the values: 0, 5, 0, 8, 3.

Formula: $=MIN(IF(B1:B5<>0, B1:B5))$

Result: 3

Here, we use an array formula that evaluates each cell within the range. If a cell's value isn't zero, it's considered for the MIN function.

Finding the Minimum Based on a Condition

Combine the MIN function with an IF statement when you need the smallest value based on specific criteria.

Scenario: Imagine cells C1 list sales amounts (200, 150, 100, 250, 175) and D1 list salespeople (John, Jane, John, Mary, Jane). You want to determine John's lowest sale.

Formula: $=MIN(IF(D1:D5="John", C1:C5))$

Result: 100

This formula evaluates only the sales amounts associated with John, returning his smallest sale value.

Highlight the Smallest Number in Excel

You can use Excel's Conditional Formatting feature with the MIN function to visually emphasize the smallest number in a dataset.

Steps:

1. Select the range you want to format (e.g., E1:E5).
2. Navigate to the 'Home' tab and click 'Conditional Formatting.'
3. Choose 'New Rule' and 'Use a formula to determine which cells to format.'
4. Enter the formula referencing your range, e.g., $=E1=MIN($E$1:$E$5)$.
5. Click on 'Format,' choose your desired highlighting options, and then click 'OK.'
6. Click 'OK' again to apply the conditional formatting.

Now, the smallest number in the range E1:E5 will be highlighted, making it easy to identify.