Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Fysh
 
Posts: n/a
Default Trouble with Averaging across several worksheets

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")

  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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" wrote:

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")

  #3   Report Post  
hrlngrv - ExcelForums.com
 
Posts: n/a
Default

Peo Sjoblom wrote...
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.

...

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

=AVERAGE('PSNS:TRF KB'!D4)

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

Use your Bodum and give up cold coffee for good!
  #4   Report Post  
Fysh
 
Posts: n/a
Default

Thanks, that worked, I used the second version.

"Peo Sjoblom" wrote:

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" wrote:

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")

  #5   Report Post  
Fysh
 
Posts: n/a
Default

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.

"Peo Sjoblom" wrote:

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" wrote:

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")



  #6   Report Post  
Fysh
 
Posts: n/a
Default

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

"Fysh" wrote:

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.

"Peo Sjoblom" wrote:

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" wrote:

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")

  #7   Report Post  
hrlngrv - ExcelForums.com
 
Posts: n/a
Default

Fysh wrote...
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.

...
"Peo Sjoblom" wrote:
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("'"
&H1:H25&"'!D4"),"<0"))

...

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

=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' to
refer to cells evaluating to that error value is as likely as any
other meaning you may have had in mind. If you mean cells containing
nothing, no formula or constant numbers or text, then the proper
Excel term is 'blank', as in the ISBLANK formula.

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

=AVERAGE('PSNS:TRF KB'!D4)

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

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

=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 array
argument will return something that works like an array of range
references. Such arrays are undocumented in Excel, which is why I'll
only say they work like such rather than that they are such. Excel
won't accept them as arithmetic operands, but N() will convert them
to arrays of values, which Excel does accept. Lots of fun making
Excel function like a 3D spreadsheet.

Actually, the formula above will skip cells evaluating to any error
value. To restrict filtering just to those cells evaluating to #N/A
specifically, you'd need a longer formula involving the ERROR.TYPE
function.
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!
  #8   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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)



"Fysh" wrote in message
...
Never mind I used 0 and it seems to work. But I still have to change the
last part for each cell.

"Fysh" wrote:

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.

"Peo Sjoblom" wrote:

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" wrote:

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")



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Number of worksheets Mark Excel Discussion (Misc queries) 1 January 18th 05 11:05 AM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM
data entry on multiple worksheets diosdias Excel Discussion (Misc queries) 1 December 7th 04 06:33 PM
Assigning Cells in worksheets to other data in other worksheets. David McRitchie Excel Discussion (Misc queries) 0 November 27th 04 07:15 PM
Sort/Link Worksheets Debby Excel Worksheet Functions 0 November 12th 04 08:50 PM


All times are GMT +1. The time now is 01:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"