In this example, the goal is to return the smaller of two values which appear in columns B and C. Although this problem could be solved with the IF function (see below), the simplest solution is to use the MIN function.
The MIN function returns the smallest numeric value in the data provided. In Excel, it's common to use the MIN function with a range like this:
=MIN(range) // minimum value in range
However, because MIN can accept values in separate arguments, you can easily use the MIN function to select the smaller of values in two cells like this:
=MIN(A1,B1) // smaller of A1 or B1 =MIN(A1,C1) // smaller of A1 or C1
This is the approach used in the worksheet shown, where the formula in E5 is:
As the formula is copied down, it returns the value in column B or the value in column C, whichever is smaller. The MIN function can be used to return the smallest value from any type of numeric data. This means you can use MIN to solve a variety of "smallest of" problems:
- Earlier of two dates
- Earlier of two times
- Faster of two times
- Colder of two temperatures
- Smaller of two fractions
Alternative to IF function
As this example shows, the MIN function can be used as a compact and elegant replacement for the IF function. For example, in the example shown, the IF function could be used to return the smaller of the two values like this:
The basic translation of this formula is "If B5 is less than C5 return B5. Otherwise return C5". This is a perfectly valid Excel formula, and you will often encounter IF formulas that follow this structure. However, the MIN version of the formula is simpler and contains no redundant references, so less prone to errors:
Note that the references to B5 and C5 appear just once in the MIN version.