Minimum value

In this example, the goal is to get the minimum quiz score (i.e. the lowest quiz score) for each person listed in column B from the five quiz scores that appear in columns C through G. This is a job for the MIN function or the SMALL function, as explained below.

MIN function

The MIN function accepts one or more arguments, which can be a mix of constants, cell references, and ranges. MIN will return the minimum value in the data provided. Text values and empty cells are ignored.

In this example, each student has five test scores in the same row, and the goal is to get the minimum score for each student. Because the quiz scores are all together, the data is supplied to MIN as a single range. The formula in I5 is:

=MIN(C5:G5)

As the formula is copied down the table, MIN returns the lowest score for each student. MIN is fully automatic. If data changes, MIN will automatically recalculate. Note that the MIN function will ignore empty cells, but it will return an error if the data contains an error. To calculate a minimum value while ignoring errors you can adapt the formula approach explained here.

SMALL function

The SMALL function can also be used to return the minimum value in a set of data. The generic syntax for SMALL looks like this:

=SMALL(range,n)

where n is a number like 1, 2, 3, etc. For example, you can retrieve the first, second, and third smallest values like this:

=SMALL(range,1) // first smallest
=SMALL(range,2) // second smallest
=SMALL(range,3) // third smallest

In this example, you could use SMALL to get the minimum quiz score for each person like this:

=SMALL(C5:G5,1)

The SMALL function is especially useful when you want to get other nth smallest values, as seen in this example.

Notes

• To get a minimum value with one or more criteria, see the MINIFS function. You can also use the FILTER function with the MIN function, as explained here.
• To get the nth smallest value in a set of data (i.e. 1st smallest, 2nd smallest, 3rd smallest, etc.), see the SMALL function.