Trouble with Averaging across several worksheets

[Fysh]

I was hoping someone could assit me on this. I have several workbooks which
update a main workbook. On this workbook I have several worksheets with a
main worksheet, which I am trying to gather averages of several cells.

I am using the reference name function for the main workbook. So even though
the cell shows a value if I click on the cell, the formula bar shows =name
which is associated with it.

I don't know if what I just mentioned has anything to do with it, but I have
tried several different formulas but none seem to work. I keep getting
#VALUE!

Here is one which doesn't seem to work. Could someone assist me on this?
Thanks

=SUM('PSNS:TRF KB'!D4)/COUNTIF('PSNS:TRF KB'!D4,"<>0")

 

[Peo Sjoblom]

You can't use countif over multiple sheets, there is a workaround but it
requires some extra work

=SUM('PSNS:TRF
KB'!D4)/SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!D4"),"<>0"))

where MySheets is a named range that holds a list of ALL you sheet names
that you want to include, not only the first and last sheets like in the SUM
formula, you need to put all the sheet names. Of course it does'nt have to be
a named range, if you have 25 sheets you can use for example

=SUM('PSNS:TRF KB'!D4)/SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H25&"'!D4"),"<>0"))



Regards,

Peo Sjoblom

 

[hrlngrv - ExcelForums.com]

Peo Sjoblom wrote..

You can't use countif over multiple sheets, there is a workaroun but i
requires some extra wor

=SUM('PSNS:TRF KB'!D4)
SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!D4"),"<>0")

where MySheets is a named range that holds a list of ALL yo shee
names that you want to include, not only the first and last sheet like i
the SUM formula, you need to put all the sheet names

..

While this works, it may be better in the long run for the OP t
modify the cells that are currently evaluating to 0 so that the
evaluate to "" instead. Once that change were made, multipl
worksheet averages could be calculated wit

=AVERAGE('PSNS:TRF KB'!D4

But this rests on an assumption that the OP is using IF functions i
these cells that return strictly positive values when the conditio
is satisfied and zeros otherwise. If this isn't the case, then th
OP's original formula, your formula and my formula and modification
all miss the point - what do the zeros represent? If the values i
these cells could legitimately be nonpositive, then excluding zer
values will bias the averages, which is usually a very bad thing. I
the cells are actually blank, no need to exclude them

 

[Fysh]

Thanks, that worked, I used the second version.

You can't use countif over multiple sheets, there is a workaround but it
requires some extra work

=SUM('PSNS:TRF
KB'!D4)/SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!D4"),"<>0"))

where MySheets is a named range that holds a list of ALL you sheet names
that you want to include, not only the first and last sheets like in the SUM
formula, you need to put all the sheet names. Of course it does'nt have to be
a named range, if you have 25 sheets you can use for example

=SUM('PSNS:TRF KB'!D4)/SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H25&"'!D4"),"<>0"))



Regards,

Peo Sjoblom

 

[Fysh]

Ok Like I said before your solution worked. However, I have a couple
questions. First how do you use this if the cell is null or has N/A it, to
not include these when divding? Second how do you drag the formula to the
other cells? For some reason I have to go in and edit each cell on the main
worksheet.

Thanks for any input.

 

[Fysh]

Never mind I used >0 and it seems to work. But I still have to change the
last part for each cell.

 

[hrlngrv - ExcelForums.com]

Fysh wrote..

Ok Like I said before your solution worked. However, I have coupl
questions. First how do you use this if the cell is null or ha N/A it, t
not include these when divding? Second how do you drag th formula t
the other cells? For some reason I have to go in and edit eac cell o
the main worksheet ..
"Peo Sjoblom" wrote

..

Last question first. If you need to be able to fill the formula int
different cells, then you have to make the INDIRECT referenc
relative. If you want to start off referencing the D4 cells in th
active cell's formula, us

=SUM('PSNS:TRF KB'!D4
/SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H25&"'!
&CELL("Address",D4)),"<>0")

As for your first question, what do you mean by a cell being 'null'
There's a specific error value #NULL!, and using the term 'null' t
refer to cells evaluating to that error value is as likely as an
other meaning you may have had in mind. If you mean cells containin
nothing, no formula or constant numbers or text, then the prope
Excel term is 'blank', as in the ISBLANK formula

If by 'null' you mean blank cells, you can't exclude them usin
COUNTIF. Note that the AVERAGE function skips blank cells, s

=AVERAGE('PSNS:TRF KB'!D4

would work if it were only blank cells (and cells containing text an
boolean values) you wanted to exclude from your averages

As for cells evaluating to #N/A, they'd be included in SUM, so you
numerator would evaluate to #N/A, so the denominator would b
irrelevant - your 'average' would be #N/A. If you want to filter ou
cells evaluating to #N/A from 3D references, you're going to have t
use the explicit approach. With a complete list of worksheet name
in, say, X1:X20 (change as needed), try the array formul

=AVERAGE(IF(ISNUMBER(1/N(INDIRECT("'"&$X$1:$X$20&"'!
&CELL("Address",D4)))),N(INDIRECT("'"&$X$1:$X$20&"'!
&CELL("Address",D4))))

Note: the N() function calls are *MANDATORY*. INDIRECT passed an arra
argument will return something that works like an array of rang
references. Such arrays are undocumented in Excel, which is why I'l
only say they work like such rather than that they are such. Exce
won't accept them as arithmetic operands, but N() will convert the
to arrays of values, which Excel does accept. Lots of fun makin
Excel function like a 3D spreadsheet

Actually, the formula above will skip cells evaluating to any erro
value. To restrict filtering just to those cells evaluating to #N/
specifically, you'd need a longer formula involving the ERROR.TYP
function

 

[Peo Sjoblom]

If you mean that you want D4 to increment to D5 and so on?

SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!"&CELL("address",D4)),">0"))

is you use a range with your sheet names make sure it has absolute reference
like

$H$2:$H$25

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)