Conditional Average across worksheets
 

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,Anthon y!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

Conditional Average across worksheets
 

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(IND IRECT("'"&H1:H6&"'!B4"))))

Biff

"madduck" wrote in
message ...

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,Anthon y!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

Conditional Average across worksheets
 

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 :rolleyes:

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

Conditional Average across worksheets
 

=AVERAGE(IF(N(INDIRECT("'"&H1:H6&"'!B4"))<0,N(IND IRECT("'"&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" wrote in
message ...

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 :rolleyes:

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