ExcelBanter - Conditional Formatting discarding zeros

"Jwest616" wrote:
I need to make the following formula disregard blanks
and zeros. Please help.
=IF(COUNT(B10:B62),AVERAGE(B10:B62))

You fix this with changes to the formula, not with a Conditional Format.

AVERAGE already ignores empty cells and cells with text, which includes
cells that appear blank, but they actually contain the null string.

To ignore zeros, it depends on what version of the Excel you are using or
targeting.

If you use Excel 2007 or later and you do not require Excel 2003
compatibility, use the following formula:

=IFERROR(AVERAGEIF(B10:B62,"<0"),"")

If you need Excel 2003 compatibility, use the following array-entered
formula (press ctrl+shift+Enter instead of just Enter):

=IF(COUNTIF(B10:B62,"<0")0,AVERAGE(IF(B10:B62<0 ,B10:B62)),"")

The change from COUNT to COUNTIF avoids a #DIV/0 error when no cells
qualify.

Alternatively, you can use the following normally-entered formula (just
press Enter as usual):

=IF(COUNTIF(B10:B62,"<0"),
SUMPRODUCT(--(B10:B62<0),B10:B62)/COUNTIF(B10:B62,"<0"),"")

Change "" (null string) to zero if you prefer. But in that case, you can
simplify the last formula as follows:

=SUMPRODUCT(--(B10:B62<0),B10:B62)/MAX(1,COUNTIF(B10:B62,"<0"))

In the SUMPRODUCT expression, the double negative converts TRUE and FALSE to
1 and 0, as SUMPRODUCT requires for your purposes.