=average(range) ignore blanks & zeros

[Aladin Akyurek]

=AVERAGE(IF(Range,Range))

which must be confirmed with control+shift+enter instead of just with enter.

 

[Guest]

How can I "average" excluding blanks and zeroes in a range?

 

[Guest]

Thanks, but it did not work. Maybe because I am summing
the same cell across multiple worksheets. For example, my
formula of =AVERAGE('Butler:8'!K78) works but if one cell
is 0 it does not exclude it.

 

[Peo Sjoblom]

You would need to use a workaround since array formulas do not work over
multiple sheets

=SUM(First:Last!K78)/SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A8&"'!K78"),"<>0"))

if there is no naming system like sheet1, sheet2 and so on then it is easier
to put the sheet names
in a range like the above where the sheet names would be in the A2:A8 range,
If you use Sheet2:Sheet4
with a naming system then you can use

=SUM(Sheet2:Sheet4!K78)/SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("2
:4"))&"'!K78"),"<>0"))

 

[Aladin Akyurek]

Also, if you download & install the morefunc.xll add-in from
http://longre.free.fr/english/index.html, insert 2 additional sheets named
First and Last, and put the relevant sheets between First and Last, you can
have:

=AVERAGE(IF(SETV(THREED(Sheet1:Sheet3!K78)),GETV()))

which still must be confirmed with control+shift+enter instead of just with
enter.

 

[Aladin Akyurek]

Just for the consistency with description I gave...

=AVERAGE(IF(SETV(THREED(First:Last!K78)),GETV()))

 

[Tom Ogilvy]

Another possibility which may be simpler in the long run.
Assuming the K78 cell's values are produced by a formula, structure the
formula to return a string

=if(formula=0,"",formula)

Average ignores non-numeric entries (except error values).

 

[Gilles Desjardins]

Another Way: =sum(range)/Countif(range,">0")
HTH

Gilles Desjardins