Microsoft KB Archive/125994

= Microsoft Knowledge Base =

Excel: Suppressing Zeros and Error Values in QUARTILE Function
Last reviewed: September 12, 1996

Article ID: Q125994

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 5.0, 5.0c
 * Microsoft Excel for the Macintosh, version 4.0, 5.0
 * Microsoft Excel for Windows NT, version 5.0

SUMMARY
In Microsoft Excel, you can use the QUARTILE function to return the quartile of a data set. In some instances, you may want to suppress zeros from being calculated into the array for the quartile function. This article gives three examples of the ways you can use the quartile function: one example does not include zeros in the array, another example checks for any error values in the array, and the final example performs both of these tasks.

MORE INFORMATION
These three examples assume that "Range" is the range of cells on the worksheet containing the numbers from which you want to perform the quartile, and "Quart" is the quart you want to return.

To keep zeros from being calculated in the quartile, use this function:

=QUARTILE(IF(RANGE<>0,RANGE),QUART) To check for error values, use this function:

=QUARTILE(IF(NOT(ISERROR(RANGE)),RANGE),QUART) To suppress zeros and to check for error values, use this function:

=QUARTILE(IF(NOT(ISERROR(RANGE)),IF(RANGE<>0,RANGE)),QUART) NOTE: The above formulas must be entered as array formulas. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.