Microsoft KB Archive/63427

{|
 * width="100%"|

Excel: MAX or MIN Function Does Not Ignore Error Values

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
 * Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 5.0

-

SUMMARY
Page 179 of the &quot;Excel 5 Worksheet Function Reference&quot; and pages 269 and 275 of the version 4.0 &quot;Microsoft Excel Function Reference,&quot; pages 149 and 152 of the version 3.0 &quot;Microsoft Excel Function Reference,&quot; and page 58 of the version 2.2 &quot;Microsoft Excel Functions and Macros&quot; manuals, incorrectly state that error values in an array are ignored when they are included as arguments in MAX or MIN functions.

MORE INFORMATION
Although in Microsoft Excel empty cells, logical values, and text are ignored when they occur in an array in a MAX or MIN function, error values are returned in these functions.

To avoid receiving error values, create a formula that checks the cells for errors before finding the maximum or minimum value, as in the following example:

  =MAX(IF(ISERROR(range),&quot;&quot;,range))

-or-

=MIN(IF(ISERROR(range),&quot;&quot;,range))

where &quot;range&quot; is the range of cells for which you are trying to find the maximum or minimum value (press COMMAND+ENTER if you are using Microsoft Excel for the Macintosh, and press CONTROL+SHIFT+ENTER if you are using Microsoft Excel for Windows or OS/2.)