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.

REFERENCES

In Microsoft Excel 5.0, for more information on the Quartile function, choose the Search button in Help, and type the following: 

   Quartile
"Microsoft Excel Function Reference," version 4.0, page 342 "Microsoft Excel User's Guide 1," version 4.0, page 157

KBCategory: kbusage
KBSubcategory:

Additional reference words: 4.00 percent percentile quarter mid max median



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.