Average Function

[carl]

I would like to take an average but would like to
exclude "zero" values and blank cells. Is there a way to
do this ?

Thank you in advance

 

[Leo Heuser]

Carl

One way:

=AVERAGE(IF(A2:A100<>0,A2:A100))

This is an array formula and it must be entered with
<Shift><Ctrl><Enter>, also if edited later.

 

[Dave R.]

=AVERAGE(IF(D36<>0,D36))

enter this with CTRL SHIFT ENTER, not just enter (which will give you
#VALUE!)

Average wont count blank cells anyways so it just excludes 0s.

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100<>0,A1:A100))