I need to return "" instead of 0

[dave roth]

Hello:

My spreadsheet counts occurrences of incidents by month; I need to average
the number of actual occurrences without including zero values in the average.

the months row I want to average is populated by:
=IF(ISBLANK(Month_Vic_2005),"",COUNTIF(Month_Vic_2005,"1"))

where Month_Vic_2005 is a named range that populates from another named
range that contains dates of occurrence. (COUNTIF "1" is replaced by 2,3 4,
etc. for later months in series.) I suspect the problem is that the ISBLANK
function is returning FALSE because the named range in question contains the
formula:
=IF(ISBLANK(Vic_Incident_Date_2005),"",MONTH(Vic_Incident_Date_2005)).

Where there is no occurrence (say for April) I want the cell value blank,
not zero. The zeroes are causing the average function to return a false value
for the months for which I actually have data.

I'm probably approaching this from the wrong end; I've tried IF(range = 0,

0, <1, is null, NOT.

Tia for the help.

 

[Bob Phillips]

Try

=AVERAGE(If(rng<>0,rng))

which is an array formula, so commit with Ctrl-Shift-Enter

 

[dave roth]

Hi:

Thanks so much; that's got the average correct.