=average(range) ignore blanks & zeros

  • Thread starter Thread starter Aladin Akyurek
  • Start date Start date
A

Aladin Akyurek

=AVERAGE(IF(Range,Range))

which must be confirmed with control+shift+enter instead of just with enter.
 
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.
 
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"))
 
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.
 
Just for the consistency with description I gave...

=AVERAGE(IF(SETV(THREED(First:Last!K78)),GETV()))
 
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).
 
Back
Top