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

Also I tried this
=AVERAGE(IF(N(INDIRECT("'"&$A$1:$F$1&"'!B4"))>0,N (INDIRECT("'"&$A$1:$F$1&"'!B4"))))

where a1:f1 contains the sheet names...but it just returns a #Name? error

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> madduck
> ------------------------------------------------------------------------
> madduck's Profile:
> http://www.excelforum.com/member.php...o&userid=36313
> View this thread: http://www.excelforum.com/showthread...hreadid=560951
>

[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

Edit: Also a big thanks to Dominc ! who was the person that posted that forumla somewhere else in the forums ( Love search !!)

[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

"madduck" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> madduck
> ------------------------------------------------------------------------
> madduck's Profile:
> http://www.excelforum.com/member.php...o&userid=36313
> View this thread: http://www.excelforum.com/showthread...hreadid=560951
>