I need to return "" instead of 0
 

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_2 005,"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_I ncident_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.

Try

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

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

--
HTH

Bob Phillips

"dave roth" wrote in message
...
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_2 005,"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_I ncident_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.

Hi:

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

"Bob Phillips" wrote:

Try

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

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

--
HTH

Bob Phillips

"dave roth" wrote in message
...
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_2 005,"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_I ncident_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.