MIN Function Excel

This Excel Tutorial demonstrates how to use the Excel MIN Function in Excel to calculate the smallest number, with formula examples.

MIN Function Description:

The MIN Function Calculates the smallest number.

Formula Examples:

min function examples

Example Formula Result
1 =MIN(B5:B10) 1
1 Other Functions:
3 =MODE(B5:B10)
4 =MEDIAN(B5:B10)
5 =MAX(B5:B10)
6

Syntax and Arguments:

The Syntax for the MIN Formula is:

Function Arguments ( Inputs ):

array – An array of numbers.

Additional Notes

The MIN Function returns the smallest number in a series. Logical values (TRUE and FALSE), and numbers stored as text are not counted. To count logical values and numbers stored as text use MINA instead.

MIN Examples in VBA

You can also use the MIN function in VBA. Type:
application.worksheetfunction.min(number1,number2)
For the function arguments (array, etc.), you can either enter them directly into the function, or define variables to use instead.

Assuming that we have the following range

Vba MIN function

 

we can get the minimum number in th range A1:D5 with the following VBA statement

Which would return 14, as this is the smallest number in that range

The MIN function can also accept a table as a parameter, so the following statement is valid as well, provided that there is a table named “Table1” in our worksheet

We can also use the MIN fuction by directly entering numbers as parameters, like the following example

which will return 1 as a  result

 

Return to the List of all Functions in Excel

How to use the MIN Function in Excel:

To use the AND Excel Worksheet Function, type the following into a cell:
=AND(
After entering it in the cell, notice how the AND formula inputs appear below the cell:
min formula syntax
You will need to enter these inputs into the function. The function inputs are covered in more detail in the next section. However, if you ever need more help with the function, after typing “=MIN(” into a cell, without leaving the cell, use the shortcut CTRL + A (A for Arguments) to open the “Insert Function Dialog Box” for detailed instructions:
how to use the min function in excel

For more information about the MIN Formula visit the
Microsoft Website.

How To Use The MIN Function

The MIN Function returns the smallest value from a range of values. It is the exact opposite of MAX Function.  

Here let’s use the MIN Function to find the lowest textbook price.

=MIN(B2:B9)

<example 1>

Empty Cells or Cells With Text

The MIN Function ignores cells that are empty or that contain non-numeric values.

<example 2>

MIN Date

Since Excel stores date as serial numbers, we can use the MIN Function to find the minimum date. 

=MIN(B2:B8)

<example 3>

VLOOKUP & MIN

In our above example, we used the MIN Function to find out the lowest textbook price. We can use the VLOOKUP Function to find out the cheapest textbook by using the following formula.

=VLOOKUP(MIN(A2:A9), A2:B9, 2, 0)

<example 4>

Notice how the MIN Function calculates the lowest textbook price and returns that value to the VLOOKUP Function. Afterwards, the VLOOKUP Function returns the textbook associated with the lowest price by searching the second column (as indicated by “2” in our formula) of the table.

Note: For the VLOOKUP Function to work, the textbook prices must be placed in the first column.

MINIFS Function

MINIFS Function returns the minimum value from a set of values by applying one or more conditions. Let’s say we want to find the lowest textbook price that has been sold out.

=MINIFS(C2:C9, B2:B9, “Sold”)

<example 5>

Notice how the formula looks for the “Sold” status from B2:B9 and creates a subset of textbook price. The minimum value is then calculated from only the relevant set of data – the sold textbooks. 

To learn more about how the MINIFS Function works, read our tutorial on MAXIFS and MINIFS.

Note: MINIFS Function is only available in Excel 2019 or Excel 365. If you have an older Excel version, you can use Array Function to make your own MIN IF. 

MIN IF 

If you are using older Excel versions, you can combine IF Function with MIN Function using an Array Function. Taking the same example as above, use the formula:

=MIN(IF(B2:B9 = “Sold”, C2:C9))

<example 6>

Note: When building array functions, you must press CTRL + SHIFT + ENTER instead of just ENTER after creating your formula.

You’ll notice how the curly brackets appear. You can not just manually type in the curly brackets; you must use CTRL + SHIFT + ENTER.

The IF Function helps narrow down the range of data and MIN Function calculates the minimum value from that subset. In this way, the price for the cheapest text that has been sold can be calculated. 

Note: The above formula uses absolute references (the $ signs) to lock cell references when copying formulas. If you aren’t familiar with this, please read our Excel References Guide