Conditional Average across worksheets

[madduck]

Hi all,

Was wondering if anyone could help.

I have 6 worksheets each containing a table as so

A4 down has dates
B4 down has one set of data "quality"
C4 down has one set of data "score"
etc... across to G4.

I want to set up another sheet that contains the average of the
corresponding cells
ie:=SUM(Andrew!B4,Lucy!B4,Corina!B4,Owen!B4,Anthony!B4,Bruce!B4)/6

My problem is sometimes there is do data in one of the cells :ie
Owen!B4.value = 0

I want the average of the cells excluding the cells that have a zero.

I can do this using
={AVERAGE(IF(B3:B16<>0, B3:B16,""))} for data on the same sheet, but
how do I do it across sheets?

Thanks Much

 

[Biff]

Hi!

Make a list of sheet names:

H1 = Andrew
H2 = Lucy
H3 = Corina
etc

Array entered:

=AVERAGE(IF(N(INDIRECT("'"&H1:H6&"'!B4"))<>0,N(INDIRECT("'"&H1:H6&"'!B4"))))

Biff

 

[madduck]

OMG !!

Thanks for the Reply Biff,

as you can see I tried that already (just with different cell
range)


But thanks to your Post I noticed that my formula had a space between N
& (

after removing this it now works.... yah !

If anyone can explain HOW this formula works, I would also apprieciate
it, I hate using things without know why

anyway thanks again Biff

 

[Biff]

=AVERAGE(IF(N(INDIRECT("'"&H1:H6&"'!B4"))<>0,N(INDIRECT("'"&H1:H6&"'!B4"))))

The Indirect function evaluates to an array of sheet ranges (even though the
range size is a single cell). Without the N function this would cause a
#VALUE! error. I've seen some people refer to this as "dereferencing". So
the N function passes the array as the numeric values. Not much of an
explanation but I don't know the exact technical reason. I just know that
this behavior is present and how to get around it. Harlan Grove can explain
it really well in technical terms.

Biff