Calculating averages over multiple sheets (excluding zeros)

[David Schuler]

I have to calculate a total average of multiple sheets. This is the
formula I have so far.

=SUM(Baehman:Vandenberg!$M$49)/COUNT(Baehman:Vandenberg!$M$49,">0")

However, the result is still the average of all cells, INCLUDING the
zeros! The cell reference (M49) has another formula in it:

=IF(J45>0,(QUOTIENT((E45/J45)*100,1)),0)

The reason for the IF statement is because without data in J45, M49
returns #DIV/0 error. And there will be instances when both J45 and
M49 SHOULD equal 0.

Can someone help me get these formulas working? I appreciate it and
so does what is left of my hair....

 

[Harlan Grove]

I have to calculate a total average of multiple sheets. This is the
formula I have so far.

=SUM(Baehman:Vandenberg!$M$49)/COUNT(Baehman:Vandenberg!$M$49,">0")

...

COUNT is *NOT* COUNTIF, so the second (string) argument to COUNT isn't counted,
and doesn't restrict the count to positive values. COUNTIF won't work directly
because it doesn't accept 3D reference first arguments.

You'll need to create a list of all worksheet names from Baehman to Vandenberg
inclusive of both. Maybe name that list WSLST. Then use a formula like

=SUM(Baehman:Vandenberg!$M$49)/MAX(1,
SUMPRODUCT(COUNTIF(INDIRECT("'"&WSLST&"'!"&CELL("Address",$M$49)),">0")))

which will return 0 when there are no positive values in M49 on any of the
worksheets.

 

[David Schuler]

...
..

COUNT is *NOT* COUNTIF, so the second (string) argument to COUNT isn't counted,
and doesn't restrict the count to positive values. COUNTIF won't work directly
because it doesn't accept 3D reference first arguments.

You'll need to create a list of all worksheet names from Baehman to Vandenberg
inclusive of both. Maybe name that list WSLST. Then use a formula like

=SUM(Baehman:Vandenberg!$M$49)/MAX(1,
SUMPRODUCT(COUNTIF(INDIRECT("'"&WSLST&"'!"&CELL("Address",$M$49)),">0")))

which will return 0 when there are no positive values in M49 on any of the
worksheets.

Thanks for the response! I'll give that a try when I get back some
time to work on it. A quick question, since I'm not an excel guru by
any means...

When you say 'create a list' you mean create a whole new sheet with
the names of the other sheets in it? Say A1 is Baehman all the way to
A12 which is Vandenberg? Or do you mean just add a list to one of the
existing sheets? Or is there actually something in Excel called
"lists" that can be created seperately from worksheets?

I'm using Excel 2000, btw.

Thanks.

~ Dave

 

[Harlan Grove]

...
...

When you say 'create a list' you mean create a whole new sheet with
the names of the other sheets in it? Say A1 is Baehman all the way to
A12 which is Vandenberg? Or do you mean just add a list to one of the
existing sheets? Or is there actually something in Excel called
"lists" that can be created seperately from worksheets?

There's no 'lists' as a speccific kind of data object separate and distinct from
arrays or ranges. I was using the term in a generic sense.

Any previously empty range would do. For that matter, if there were only a few
worksheets involved ('few' being subjective), you could define a name referring
to an array constant, e.g., define the name WSLIST referring to

={"Sheet1","Sheet2","Sheet3"}

With 12 worksheets, you'd be better off entering the worksheet names in a range.
Using a separate worksheet to do so could be a good idea in terms of
organization, but it incurrs additional overhead (more RAM needed when open,
larger workbook file when saved). Up to you.

To repeat, either enter each worksheet name in separate but sequential cells in
a range or use constant arrays of text as defined names.

 

[David Schuler]

There's no 'lists' as a speccific kind of data object separate and distinct from
arrays or ranges. I was using the term in a generic sense.

Any previously empty range would do. For that matter, if there were only a few
worksheets involved ('few' being subjective), you could define a name referring
to an array constant, e.g., define the name WSLIST referring to

={"Sheet1","Sheet2","Sheet3"}

With 12 worksheets, you'd be better off entering the worksheet names in a range.
Using a separate worksheet to do so could be a good idea in terms of
organization, but it incurrs additional overhead (more RAM needed when open,
larger workbook file when saved). Up to you.

To repeat, either enter each worksheet name in separate but sequential cells in
a range or use constant arrays of text as defined names.

Ok, I got it. I went with the list and, with enough trial and error, I
got it working. Suffice to say, I can now create and reference lists!

Thanks a bundle, Harlan!