Excel: MAX or MIN Function Does Not Ignore Error Values

• Microsoft Excel for Windows, version 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))

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