How do i get an average that ignores blanks in the range of cells.

[Guest]

i am trying to average a range of cells, in different spreadsheets within one
worksheet but it gives me the error #div/0 when one of the cells is blank.
Any suggestions, thoughts, comments?

 

[anilsolipuram]

Try this

=SUM(D7:E11)/COUNT(D7:E11

 

[Guest]

Not quite sure what formula you're using - =AVERAGE should discount blank
cells. Could you perhaps copy the formula that's giving you a problem into a
post?

 

[Frank Kabel]

Hi
try
=IF(COUNT(A1:A20),AVERAGE(A1:A20),"no cells filled")

 

[Frank Kabel]

Hi
better IMHO:
=AVERAGE(D7:E11)

 

[Guest]

=AVERAGE(BVB!O41,HMC!O41,VVRMC!O41,VBMC!O41) This is the formula i have set
up. If i run it as it is i get the error, if i remove the HMC one, which is
blank, i get an average.

 

[Guest]

Tom,
in the last post i gave you the formula that i am using. I don't know if
this is pertinent or not, but the cells i am pulling from all have =average
formulas in them. What i am really trying to do is get an average of the
averages. those formulas are all the same =average(05:035). that's oh 5 and
oh 35, not zeros.

 

[Guest]

Howdy,

I played around with this a bit and I think your problem is that you have
some ranges that are all blank. AVERAGE needs at least 1 non-blank cell to
determine a result. That's why your AVERAGE function gives you the #DIV/0
error. You can get around the problem several different ways, including the
suggestions others have put in this thread. If you want to keep using an
AVERAGE function, though, you'll have to add a conditional to your formulas
to take care of the event where all your cells in the range are blank. The
reasoning here is to decide whether the range is empty or not before applying
the AVERAGE function. One way to do it follows:

=IF(COUNT(O5:O35)=0,0,AVERAGE(O5:O35))

Good luck,

Tom Hayakawa