Averaging Cells Across Spreadsheets, Skipping Zero Values

Posted by Jeremy on May 04, 2001 5:27 PM

I'm trying to average the same cell on 31 separate sheets in one workbook, the syntax =AVERAGE('1:31'!C12) Includes zero values, which I do not want counted.

I Tried a SUM/COUNTIF with a >0 argument, but the COUNTIF is returning a #VALUE error message.

I know there has to be an averaging function that can ignore zero values, but I cannot find it. If anyone can give me a pointer I would be greatly appreciative.

Thanks

valblaze@pacbell.net

Posted by joe on May 04, 2001 8:19 PM

Help with financial analysis

Hi,

Does anyone know where I can download a plug & play spreadsheet for finanancial anlaysis of p&l, balance sheet, working cap data etc.

Would be most grateful

Joe

Posted by Jaime on May 04, 2001 9:17 PM

Function COUNT_ACROSS_SHEETS()
Application.Volatile
SUMMER = 0
COUNTER = 0
For Each SHT In Worksheets
With SHT
If SHT.Cells(1, 1) &LT;&GT; 0 Then
SUMMER = SUMMER + SHT.Cells(1, 1)
COUNTER = COUNTER + 1
End If
End With

Next SHT

COUNT_ACROSS_SHEETS = SUMMER / COUNTER
End Function

Posted by Aladin Akyurek on May 05, 2001 1:28 AM

Jaime proposed VB, because, I guess, he knew an array formula will not work across sheets. So please use his code. What follows is just for fun.
My example uses 4 consecutive sheets instead of 31.

On some sheet,

in A1 enter: =IF(ROW()>$C$3,"",INDIRECT("Sheet"&ROW()&"!"&$C$4)) [ copy down as far as needed ]

in column B from B1 on enter the following labels: "Start","End","N","Cell","Range","Result".

in column C from C1 on enter the following labels: "Sheet1","Sheet4", C4.

in C4 enter: =MID(C2,6,LEN(C2)-5)+0

in C5 enter: ="A1:A"&C4

in C6 array-enter: =AVERAGE(IF(INDIRECT(C5),INDIRECT(C5))) [ in order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time ]

The array-formula in C6 is an abridged version of the array-formula: =AVERAGE(IF(INDIRECT(C5)&LT;&GT;0,INDIRECT(C5)))

The whole setup, including everything, should look as:

{9,"Start","Sheet1";7,"End","Sheet4";2,"N","C12";0,"Cell",4;"","Range","A1:A4";"","Result",6}

Aladin

Posted by Aladin Akyurek on May 05, 2001 2:48 AM

Oops...

While posting, I switched the values of B3:C3 and B4:C4, an action that led to some inaccurate references. Hence, corrections.

in A1 enter: =IF(ROW()>$C$4,"",INDIRECT("Sheet"&ROW()&"!"&$C$3)) [ copy down as far as needed ]

in column B from B1 on enter the following labels: "Start","End","Cell","N","Range","Result".

in column C from C1 on enter the following labels: "Sheet1","Sheet4", C12.

{9,"Start","Sheet1";7,"End","Sheet4";2,"Cell","C12";0,"N",4;"","Range","A1:A4";"","Result",6}