AVERAGE COLUMNS BUT NOT COUNT EMPTY CELLS

[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

 

[Harald Staff]

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

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?

 

[Frank Kabel]

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

 

[Ken Wright]

=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
----------------------------------------------------------------------------

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?

 

[Ken Wright]

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))}