Average when No Data

[carl]

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.

 

[Bob Phillips]

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[David Billigmeier]

Change to:

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

 

[Biff]

Hi!

One way (array entered):

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

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

Biff

 

[Dave Peterson]

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.