Excel: Returning the Second Minimum or Maximum in a Range

ID: Q79267

The information in this article applies to:

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)))
You must enter the formula in cell A7 as an array formula by pressing CTRL+SHIFT+ENTER simultaneously.

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))))
Again, enter this formula as an array with the CTRL+SHIFT+ENTER keystroke. These same array formulas can be used to generate the second maximum value by replacing MIN with MAX and vice versa throughout the formula.

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

Additional query words: 2.1 2.10 2.2 2.20 2.21 3.0 3.00 SUMIF biggest most least greatest highestlowest quartile distribution finding nth 

Keywords          :  
Version           : WINDOWS:2.1,3.0,97
Platform          : WINDOWS

Last Reviewed: March 24, 1999