Excel: Returning the Second Minimum or Maximum in a Range

• Microsoft Excel for Windows, version 2.1, 3.0
• Microsoft Excel for OS/2, version 2.2, 3.0
• Microsoft Excel 97 for Windows

SUMMARY

You can return the second smallest or second largest value in a range or list of values in Microsoft Excel by using a special adaptation of the MIN or MAX function entered as an array formula.

MORE INFORMATION

The MIN function returns the smallest value from a range on a worksheet or a list of values. To return the next smallest value (the second MIN) of a range, take the MIN of a list of values that excludes the first MIN.

Example

Enter the following on a blank sheet:

   A1: 1.2
   A2: 3.4
   A3: 2.3
   A4: 6.7
   A5: 5.6
   A6:
   A7: =MIN(IF(A1:A5<>MIN(A1:A5),A1:A5,MAX(A1:A5)))

This returns the value 2.3, the second smallest value.

If you would like to return the minimum value when there are two or more of the same minimum values (instead of the next minimum, which would really be the third minimum value), you need to add another condition, which checks for duplicate minimum values:

   =IF(SUM(IF(A1:A5=MIN(A1:A5),1,0))>1,MIN(A1:A5),
       MIN(IF(A1:A5<>MIN(A1:A5),A1:A5,MAX(A1:A5))))

These formulas can also be used to perform the same operation on an array of values. Simply replace the ranges with the name of the array.

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 149, 152