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") |
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") |
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! |
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") |
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") |
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") |
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! |
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") |
All times are GMT +1. The time now is 06:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com