Average when No Data
 

I use this formula:

=AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7))

When there is no data in the range, the formula returns DIV/0. Is there a
way to modify the formula so that it returns """ instead of DIV/0.

Thank you in advance.

Average when No Data
 

=IF(COUNT(I7:AQ7)=0,"",AVERAGE(IF(ISNUMBER(I7:AQ7) ;I7:AQ7)))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"carl" wrote in message
...
I use this formula:

=AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7))

When there is no data in the range, the formula returns DIV/0. Is there a
way to modify the formula so that it returns """ instead of DIV/0.

Thank you in advance.

Average when No Data
 

Change to:

=IF(AND(NOT(ISNUMBER(I7:AQ7))),"",AVERAGE(IF(ISNUM BER(I7:AQ7),I7:AQ7)))

--
Regards,
Dave


"carl" wrote:

I use this formula:

=AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7))

When there is no data in the range, the formula returns DIV/0. Is there a
way to modify the formula so that it returns """ instead of DIV/0.

Thank you in advance.

Average when No Data
 

Hi!

One way (array entered):

=IF(COUNT(I7:AQ7),AVERAGE(IF(ISNUMBER(I7:AQ7),I7:A Q7)),"")

If Count = 0 the formula returns blank ("").

Biff

"carl" wrote in message
...
I use this formula:

=AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7))

When there is no data in the range, the formula returns DIV/0. Is there a
way to modify the formula so that it returns """ instead of DIV/0.

Thank you in advance.

Average when No Data
 

Since =Average() ignores text, is there a reason you used the array formula?

=if(count(I7:aq7)=0;"";average(i7:aq7))

The array formula will ignore errors (#ref, div/0, etc), though.



carl wrote:

I use this formula:

=AVERAGE(IF(ISNUMBER(I7:AQ7);I7:AQ7))

When there is no data in the range, the formula returns DIV/0. Is there a
way to modify the formula so that it returns """ instead of DIV/0.

Thank you in advance.

--

Dave Peterson