Finding Average - Discounting Zeros

[jbb897]

I am trying to find the average of many cells across multiple worksheet
while discounting zeros. Is this possible? Please see formula belo
and let me know if there is an easier way. Thanks in advance!

=sum((araopovic:white!c7)/countif(arapovic:white!">0")

 

[Frank Kabel]

Hi
COUNTIF won't work on 3D references directly. Lets try the following:
- first put the names of your sheet in a cell range. e.g. cells X1:X10
- now use the following formula:
=SUM(araopovic:white!c7)/SUMPRODUCT(COUNTIF(INDIRECT("'" & X1:X10 &
"'!C7"),"<>0"))

 

[jbb897]

Thanks Frank - so are you saying that I need to put all the data on th
same worksheet to make this work? I followed your instructions belo
and keep getting a #NAME? error.

Jeff

 

[Frank Kabel]

Hi
no, you just have to enter all sheetnames on this summary sheet. What
is the exact formula you have used?

 

[jbb897]

I listed all the sheet names in cells B:9 through B:41. These shee
names are listed in the summary sheet which is named "User Stats".

I get a #Ref! error.

=SUM(Arapovic:White!C7)/SUMPRODUCT(COUNTIF(INDIRECT("'User Stats"
B9:B41 & "'!C7"),"<>0"))

Thanks,

Jef

 

[Frank Kabel]

Hi
try:
=SUM(Arapovic:White!C7)/SUMPRODUCT(COUNTIF(INDIRECT("'" &
'user stats'!B9:B41 & "'!C7"),"<>0"))