Excel: MAX or MIN Function Does Not Ignore Error Values

ID: Q63427

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 "Excel 5 Worksheet Function Reference" and pages 269 and 275 of the version 4.0 "Microsoft Excel Function Reference," pages 149 and 152 of the version 3.0 "Microsoft Excel Function Reference," and page 58 of the version 2.2 "Microsoft Excel Functions and Macros" 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),"",range))

   -or-

   =MIN(IF(ISERROR(range),"",range))

where "range" 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.)

REFERENCES

"Excel 5 Worksheet Function Reference" page 179
"Microsoft Excel Function Reference," version 4.0, pages 269 and 275
"Microsoft Excel Function Reference" version 3.0, pages 149 and 152
"Microsoft Excel Functions and Macros" version 2.2, page 58

Additional query words: 2.20 3.0 5.00c

Keywords :
Version :
Platform :
Issue type :