Returning the Second Minimum or Maximum in a Range

ID: Q79267

The information in this article applies to:

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

SYMPTOMS

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem.

To resolve this problem, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The English version of this fix should have the following file attributes or later:

Date Time Version Size File name Platform

NOTE: If this product was already installed on your computer when you purchased it from the Original Equipment Manufacturer (OEM) and you need this fix, please call the Pay Per Incident number listed on the above Web site. If you contact Microsoft to obtain this fix, and if it is determined that you only require the fix you requested, no fee will be charged. However, if you request additional technical support, and if your no-charge technical support period has expired, or if you are not eligible for standard no-charge technical support, you may be charged a non-refundable fee.

For more information about eligibility for no-charge technical support, see the following article in the Microsoft Knowledge Base:

Q154871 Determining If You Are Eligible for No-Charge Technical Support

STATUS

Microsoft has confirmed this to be a problem in Windows 98.

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.

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

Keywords :
Version : WINDOWS:2.1,3.0,97
Platform : WINDOWS
Issue type : kbprb