I am using the average function! But when there is no data in the cells to
average it returns the #DIV?0! how can I avoid this I would like it to be
blank

=IF(SUM(A1:A4)=0,"",AVERAGE(A1:A4))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LostNFound" wrote in message
...
I am using the average function! But when there is no data in the cells to
average it returns the #DIV?0! how can I avoid this I would like it to be
blank

One way:

=IF(COUNT(A1:A10),AVERAGE(A1:A10),"")

HTH
Jason
Atlanta, GA

-----Original Message-----
I am using the average function! But when there is no
data in the cells to
average it returns the #DIV?0! how can I avoid this I
would like it to be
blank
.

Hi, Bob:

How about COUNT instead of SUM?


On Tue, 15 Mar 2005 16:19:27 -0000, "Bob Phillips"
wrote:

=IF(SUM(A1:A4)=0,"",AVERAGE(A1:A4))

Hi Myrna,

I thought about that after I sent it, especially as I thought that if there
were a similar negative and positive numbers, that would still SUM to 0. But
I then thought, it would also AVERAGE to 0, so does it really matter. So I
thought about it (ooh, for at least 10 secs), decided there was no real
disadvantage to SUM, so said no more. You can't even get rid of the =0 part
by inverting it,=IF(SUM(A1:A4),AVERAGE(A1:A4),"") so there is no loss there.

Can you think of a case where SUM would cause a problem?


"Myrna Larson" wrote in message
...
Hi, Bob:

How about COUNT instead of SUM?


On Tue, 15 Mar 2005 16:19:27 -0000, "Bob Phillips"
wrote:

=IF(SUM(A1:A4)=0,"",AVERAGE(A1:A4))

couldn´t you use
IF(isblank(A1:A10),"",average(A1:A10))?

"Bob Phillips" skrev:

Hi Myrna,

I thought about that after I sent it, especially as I thought that if there
were a similar negative and positive numbers, that would still SUM to 0. But
I then thought, it would also AVERAGE to 0, so does it really matter. So I
thought about it (ooh, for at least 10 secs), decided there was no real
disadvantage to SUM, so said no more. You can't even get rid of the =0 part
by inverting it,=IF(SUM(A1:A4),AVERAGE(A1:A4),"") so there is no loss there.

Can you think of a case where SUM would cause a problem?


"Myrna Larson" wrote in message
...
Hi, Bob:

How about COUNT instead of SUM?


On Tue, 15 Mar 2005 16:19:27 -0000, "Bob Phillips"
wrote:

=IF(SUM(A1:A4)=0,"",AVERAGE(A1:A4))