Average Founction HELP

[jlevine]

Help please - I am trying to average a range which contains empty cells. I do not want the average to include empty cells!!!!!

[BenjieLop]

Help please - I am trying to average a range which contains empty cells. I do not want the average to include empty cells!!!!!

The formula

=average(A1:A100)

will ignore the blank cells.

Regards.

[olasa]

If - for some reason - your definition of "empty" includes 0 (Zero); use this formula:

=SUM(A1:A100)/SUMPRODUCT(--(A1:A100<>0),--(ISNUMBER(A1:A100)))

The sumproduct formula will exclude:
0 (Zero)
"" (Blank)
" " (Space)...and all other 'letters'

Ola Sandström

[Domenic]

Another way, if you want to exclude zero's...

=AVERAGE(IF(A1:A10>0,A1:A10))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!