AVERAGE COLUMNS BUT NOT COUNT EMPTY CELLS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I'm trying to find the average of a bunch of different cells. I only want the cells counted if they have a number in them. I don't want to count empty cells. I have unchecked the zero values checkbox in my preferences. I tried this formula =AVERAGE(c2+c5+c6+c8) but I get an error message. Any suggestions
Thank
marcia
 
Hi Marcia

Average leaves empty cells out by default. But pass them, don't add them:
=AVERAGE(C2,C5,C6,C8)

--
HTH. Best wishes Harald
Followup to newsgroup only please

Marcia said:
Hi,
I'm trying to find the average of a bunch of different cells. I only want
Click to expand...
the cells counted if they have a number in them. I don't want to count empty
cells. I have unchecked the zero values checkbox in my preferences. I tried
this formula =AVERAGE(c2+c5+c6+c8) but I get an error message. Any
suggestions?
 
Hi Marcia,

AVERAGE does not include blank cells in its calculation 8only '0' will
be included). So you can easily use the following
=AVERAGE(c2,c5,c6,c8)

whether they are blank or not.

HTH
Frank
 
=AVERAGE(C2:C8) - I'm assuming the cells you didn't list are blanks.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Marcia said:
Hi,
I'm trying to find the average of a bunch of different cells. I only want the
Click to expand...
cells counted if they have a number in them. I don't want to count empty cells.
I have unchecked the zero values checkbox in my preferences. I tried this
formula =AVERAGE(c2+c5+c6+c8) but I get an error message. Any suggestions?
 
Note this will ignore empty cells, but not zero values. If you want both
ignored you can use an array formula:-

=AVERAGE(IF(A1:A100,A1:A100))

array entered using CTRL+SHIFT+ENTER, which if entered correctly will apear as
the following in the formula bar:_

{=AVERAGE(IF(A1:A100,A1:A100))}
 
Back
Top