Excel MIN Function
Summary
The Excel MIN function returns the smallest numeric value in a range of values. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
Purpose
Get the smallest value.
Return value
The smallest value in the array.
Syntax
=MIN (number1, [number2], ...)
Arguments
- number1 - Number, reference to numeric value, or range that contains numeric values.
- number2 - [optional] Number, reference to numeric value, or range that contains numeric values.
Usage notes
The MIN function can be used to return the smallest value from a set of data. For example, the fastest time in a race, the lowest temperature, or the smallest sales number. Arguments can be provided as constants, or as cell references or ranges, for example:
Notes:
- The MIN function ignores empty cells.
- The MIN function in current versions of Excel accepts up to 255 arguments. If arguments contain no numbers, MIN returns 0.
- Arguments can be provided as numbers, names, arrays, or references that contain numbers.
- The MIN function ignores TRUE and FALSE values, and numbers entered as text, unless they are typed directly into the list of arguments.
- To include logical values in a reference, see the MINA function.
MIN formula examples
To test if a numeric value falls between two numbers, you can use the AND function with two logical tests. In the example shown, the formula in E5 is:
=AND(D5>MIN(B5,C5),D5<MAX(B5,C5))
How this formula works...
To calculate the max or min change in a set of data as shown, without using a helper column, you can use an array formula. In the example, the formula in G6 is:
{=MAX(C5:C12-D5:D12)}
Note: this is an array formula...
To lookup information associated with the lowest value in table, you can use a formula based on INDEX, MATCH, and MIN functions.
In the example shown, a formula is used to identify the name of the contractor with the...
To get the position of the nth match (for example, the 2nd matching value, the 3rd matching value, etc.), you can use a formula based on the SMALL function. In the example shown, the formula in G5 is:
=SMALL(IF(list=...
To get the minimum value from a set of numbers, use the MIN function.
In the example shown, the formula in cell I6 is:
=MIN(C6:G6)
The MIN function is fully automatic. It accepts one or more arguments, which can be...
To calculate gas (MPG) based on odometer readings you can build a table with a few simple formulas. In the example shown, the formulas in E5 and F5 are:
=[@Mileage]-SUM(C4) // E5 calculate mileage
=[@Distance]/[@...
To calculate the number of days that overlap in two date ranges, you can use basic date arithmetic, together with the the MIN and MAX functions.
In the example shown, the formula in D6 is:
=MAX(MIN(end,C6)-MAX(start,...
To get the minimum value in a data set using multiple criteria (i.e. to get MIN IF), you can use and array formula based on the MIN and IF functions. In the example shown the formula in I6 is:
{=MIN(IF(color=G6,IF(...
To get a minimum value based on criteria, you can use the MIN function together with the IF function. In the example shown, the formula in G6 is:
{=MIN(IF(names=F6,times))}
Where "names" is the named range B6:B17,...
To calculate the minimum difference between two sets of values, ignoring cases where either value is blank, you can use an array formula based on the MIN and IF functions. In the example shown, the formula in F4 is...
To find the closest match with a lookup value and numeric data, you can use an array formula based the INDEX, MATCH, ABS and MIN functions. In the example shown, the formula in E5 is:
{=INDEX(data,MATCH(MIN(ABS(data-...
To calculate overtime and pay associated with overtime, you can use the formulas explained on this page. In formula in cell I5 is:
=(F5*H5)+(G5*H5*1.5)
How this formula works
Note: it's important to understand that...
You can get the first row (i.e. the starting row number) in a range with a formula based on the ROW function.
In the example shown, the formula in cell F5 is:
=MIN(ROW(data))
where data is a named range for B5:D10...
To calculate a percentage based value capped at a specific amount, you can use the MIN function.
In the example shown, the formula in D6 is:
=MIN(C6*10%,1000)
How this formula works
This formula takes advantage of...
To calculate the total number of hours between a start and end time that overlap a period of interest with a given start and end time, you can use a formula based on the IF, MAX, and MIN functions. In the example...
Related videos
In this video, we'll look at how to build a simple dynamic chart in Excel. A dynamic chart automatically responds to changes in data.
Named ranges make formulas easier to read and understand, and reduce errors. In this video, we'll show you how to create a named range.
Sometimes you want to reference a worksheet dynamically in a formula, so that it can be changed on the fly. In this video, we'll look at how to create a dynamic reference to a worksheet in a formula.
In this video, we'll look at how to simplify some formulas we created in a previous video, by replacing IF statements with the MIN function and a bit of boolean logic.
In this video, we'll introduce the idea of a dynamic range and show you why you might want to use one. Spoiler: they adjust formulas automatically when data changes.
Sometimes you might create named ranges after you've already built formulas. In this video, we'll look at how to apply named ranges to an existing formula.
Related functions
Good links
