Average percentage excluding zeros spanning numerous tabs

[Guest]

I've got a sheet consolidating the data from various detail sheets... I need
to create a formula for the average percentage excluding zero values... i've
got the following formula =AVERAGE(IF(A1:A52>0,A1:A52)) but that doesn't give
the option of using information from different tabs (the data is in the same
cell in each tab)... any ideas?!

Cheers

 

[Bob Phillips]

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A1:A52"),">0"))/
SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A1:A52"),">0"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Domenic]

List your sheet names in a range of cells, for example B1:B5, then try
the following formula....

=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$1:$B$5&"'!A1:A52"),">0"))/SUMPRODUCT(CO
UNTIF(INDIRECT("'"&$B$1:$B$5&"'!A1:A52"),">0"))

Hope this helps!

 

[Ragdyer]

Hey Bob,

Been working on this for a while.
I'm on an XL97 machine today and it crashed several times when I tried to
use SUM in this scenario:

With sheet names in Z1 to Z3:
This works!

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z3&"'!A1:A52"),">0"))/SUMPRODUCT(COUNTIF(I
NDIRECT("'"&Z1:Z3&"'!A1:A52"),">0"))


This crashes!

=SUMPRODUCT(SUM(INDIRECT("'"&Z1:Z3&"'!A1:A52")))/SUMPRODUCT(COUNTIF(INDIRECT
("'"&Z1:Z3&"'!A1:A52"),">0"))

Care to try it on your version?<bg>

 

[Ragdyer]

Just tried the same revision (Sumif to Sum) on your formula, and it *also*
caused my XL97 to crash!

 

[RagDyeR]

FWIW,

XL02 returned #N/A error and ... did *not* crash!

--

Regards,

RD
----------------------------------------------------------------------------