Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF
Conditional calculations across sheets isn't easy. Try this:
=SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0")) -- Biff Microsoft Excel MVP "JimG" wrote in message ... I have an AVERAGE IF formula that works fine on a single worksheet for July 1st... =AVERAGEIF('7-1'!N:N,"0",'7-1'!N:N) I need it to work across 31 worksheets, so it will accurately calculate the averages for all values greater than zero, and for the entire month. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF
AWESOME Solution! I would never had thought to try that.
Thank You very much! "T. Valko" wrote: Conditional calculations across sheets isn't easy. Try this: =SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0")) -- Biff Microsoft Excel MVP "JimG" wrote in message ... I have an AVERAGE IF formula that works fine on a single worksheet for July 1st... =AVERAGEIF('7-1'!N:N,"0",'7-1'!N:N) I need it to work across 31 worksheets, so it will accurately calculate the averages for all values greater than zero, and for the entire month. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "JimG" wrote in message ... AWESOME Solution! I would never had thought to try that. Thank You very much! "T. Valko" wrote: Conditional calculations across sheets isn't easy. Try this: =SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0")) -- Biff Microsoft Excel MVP "JimG" wrote in message ... I have an AVERAGE IF formula that works fine on a single worksheet for July 1st... =AVERAGEIF('7-1'!N:N,"0",'7-1'!N:N) I need it to work across 31 worksheets, so it will accurately calculate the averages for all values greater than zero, and for the entire month. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF
What if I have to take the average of the values located in the same cell
across 10 sheets (ignoring the value if it is <=0). For instance, the sheets are labeled 'am peak_10' to 'am peak_100' and the cell location is C26. I am not quite familiar with the 'indirect' function hence not able to comprehend the above solution. Any help would be appreciated. Thanks! "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "JimG" wrote in message ... AWESOME Solution! I would never had thought to try that. Thank You very much! "T. Valko" wrote: Conditional calculations across sheets isn't easy. Try this: =SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0")) -- Biff Microsoft Excel MVP "JimG" wrote in message ... I have an AVERAGE IF formula that works fine on a single worksheet for July 1st... =AVERAGEIF('7-1'!N:N,"0",'7-1'!N:N) I need it to work across 31 worksheets, so it will accurately calculate the averages for all values greater than zero, and for the entire month. Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF
Yes, that's right. I tried to work around with the solution previously
provided, for which i renamed the sheet as 'am peak_1', 'am peak_2' and so on... as it seems to not recognize the increment of 10 (ie. 10, 20...). And it worked (with one particular cell location though)! But as the cell location is a text in the above stated formula, I am not sure how I can apply that to a group of cells. As in my case, I am looking to apply the conditional average to alternate columns. In one of the other threads under the same topic, the suggestion was to use the IF statement and create another table, replacing <0 values by "" and then calculate average. Though the solution is pretty simple, I am hoping there would be a 'one formula' solution to this problem, as I need to extract such averages for 18 scenarios (ie. 18 different excel sheets)! Thanks, Som "T. Valko" wrote: average ... the same cell across 10 sheets sheets are labeled 'am peak_10' to 'am peak_100' ignoring the value if it is <=0 If you have 10 sheets named "10" to "100" then I'm assuming the names increment by 10? 'am peak_10' 'am peak_20' 'am peak_30' 'am peak_40' etc etc 'am peak_100' -- Biff Microsoft Excel MVP "Sowmya C" <Sowmya wrote in message ... What if I have to take the average of the values located in the same cell across 10 sheets (ignoring the value if it is <=0). For instance, the sheets are labeled 'am peak_10' to 'am peak_100' and the cell location is C26. I am not quite familiar with the 'indirect' function hence not able to comprehend the above solution. Any help would be appreciated. Thanks! "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "JimG" wrote in message ... AWESOME Solution! I would never had thought to try that. Thank You very much! "T. Valko" wrote: Conditional calculations across sheets isn't easy. Try this: =SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0")) -- Biff Microsoft Excel MVP "JimG" wrote in message ... I have an AVERAGE IF formula that works fine on a single worksheet for July 1st... =AVERAGEIF('7-1'!N:N,"0",'7-1'!N:N) I need it to work across 31 worksheets, so it will accurately calculate the averages for all values greater than zero, and for the entire month. Any ideas? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF
I thought I should make myself more clear...
The values that I am looking to average out are across 10 sheets (and as previously noted in the increments of 10 named 'am peak_10', 'am peak_20'...so on...) in cell locations C26:C30, E26:E30,..., Y26:Y30 (ie. alternate columns). I was looking for a formula which would give the average of all values in 'am peak_*'!C26 excluding the cells which have '0' value (for each of the above mentioned cell location). I understand conditional statements do not work across sheets as the SUM, COUNT and AVERAGE do. It would have been so much easier if it did. :) "Sowmya C" wrote: Yes, that's right. I tried to work around with the solution previously provided, for which i renamed the sheet as 'am peak_1', 'am peak_2' and so on... as it seems to not recognize the increment of 10 (ie. 10, 20...). And it worked (with one particular cell location though)! But as the cell location is a text in the above stated formula, I am not sure how I can apply that to a group of cells. As in my case, I am looking to apply the conditional average to alternate columns. In one of the other threads under the same topic, the suggestion was to use the IF statement and create another table, replacing <0 values by "" and then calculate average. Though the solution is pretty simple, I am hoping there would be a 'one formula' solution to this problem, as I need to extract such averages for 18 scenarios (ie. 18 different excel sheets)! Thanks, Som "T. Valko" wrote: average ... the same cell across 10 sheets sheets are labeled 'am peak_10' to 'am peak_100' ignoring the value if it is <=0 If you have 10 sheets named "10" to "100" then I'm assuming the names increment by 10? 'am peak_10' 'am peak_20' 'am peak_30' 'am peak_40' etc etc 'am peak_100' -- Biff Microsoft Excel MVP "Sowmya C" <Sowmya wrote in message ... What if I have to take the average of the values located in the same cell across 10 sheets (ignoring the value if it is <=0). For instance, the sheets are labeled 'am peak_10' to 'am peak_100' and the cell location is C26. I am not quite familiar with the 'indirect' function hence not able to comprehend the above solution. Any help would be appreciated. Thanks! "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "JimG" wrote in message ... AWESOME Solution! I would never had thought to try that. Thank You very much! "T. Valko" wrote: Conditional calculations across sheets isn't easy. Try this: =SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0")) -- Biff Microsoft Excel MVP "JimG" wrote in message ... I have an AVERAGE IF formula that works fine on a single worksheet for July 1st... =AVERAGEIF('7-1'!N:N,"0",'7-1'!N:N) I need it to work across 31 worksheets, so it will accurately calculate the averages for all values greater than zero, and for the entire month. Any ideas? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF
I am looking to apply the conditional average
to alternate columns. Hmmm... That sounds like trouble! This formula will average cell C26 across your 10 sheets excluding 0 and negative values: =SUMPRODUCT(SUMIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,10}&"'!C26"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,9,10}&"'!C26"),"0")) You can shorten the formula a little by using a defined name. InsertNameDefine Name: Sheets Refers to: =10*{1,2,3,4,5,6,7,8,9,10} Then: =SUMPRODUCT(SUMIF(INDIRECT("'am peak_"&Sheets&"'!C26"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'am peak_"&Sheets&"'!C26"),"0")) -- Biff Microsoft Excel MVP "Sowmya C" wrote in message ... Yes, that's right. I tried to work around with the solution previously provided, for which i renamed the sheet as 'am peak_1', 'am peak_2' and so on... as it seems to not recognize the increment of 10 (ie. 10, 20...). And it worked (with one particular cell location though)! But as the cell location is a text in the above stated formula, I am not sure how I can apply that to a group of cells. As in my case, I am looking to apply the conditional average to alternate columns. In one of the other threads under the same topic, the suggestion was to use the IF statement and create another table, replacing <0 values by "" and then calculate average. Though the solution is pretty simple, I am hoping there would be a 'one formula' solution to this problem, as I need to extract such averages for 18 scenarios (ie. 18 different excel sheets)! Thanks, Som "T. Valko" wrote: average ... the same cell across 10 sheets sheets are labeled 'am peak_10' to 'am peak_100' ignoring the value if it is <=0 If you have 10 sheets named "10" to "100" then I'm assuming the names increment by 10? 'am peak_10' 'am peak_20' 'am peak_30' 'am peak_40' etc etc 'am peak_100' -- Biff Microsoft Excel MVP "Sowmya C" <Sowmya wrote in message ... What if I have to take the average of the values located in the same cell across 10 sheets (ignoring the value if it is <=0). For instance, the sheets are labeled 'am peak_10' to 'am peak_100' and the cell location is C26. I am not quite familiar with the 'indirect' function hence not able to comprehend the above solution. Any help would be appreciated. Thanks! "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "JimG" wrote in message ... AWESOME Solution! I would never had thought to try that. Thank You very much! "T. Valko" wrote: Conditional calculations across sheets isn't easy. Try this: =SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0")) -- Biff Microsoft Excel MVP "JimG" wrote in message ... I have an AVERAGE IF formula that works fine on a single worksheet for July 1st... =AVERAGEIF('7-1'!N:N,"0",'7-1'!N:N) I need it to work across 31 worksheets, so it will accurately calculate the averages for all values greater than zero, and for the entire month. Any ideas? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF
Wow...Thanks! That's really helpful and it surely works!
And I just changed the formula a little bit to save some labor instead of changing the cell location in each and every formula. I created a separate table which had 'C, blank, E, blank,..., Y' in the first row and '26,27,28,29,30' in each column. And the modified equation I used is: =SUMPRODUCT(SUMIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,9,10}&"'!"&C$50&C51)," 0"))/SUMPRODUCT(COUNTIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,9,10}&"'!"&C$50&C51)," 0")) {for instance, here C50 has value 'C' and C51 has value '26'} And this I could copy across the rows and columns. Thanks a lot again for you help. Really appreciate it. All I had to do was create one averaging sheet and copy that for the other 18 scenarios! It made my job so much easier! :) Som "T. Valko" wrote: I am looking to apply the conditional average to alternate columns. Hmmm... That sounds like trouble! This formula will average cell C26 across your 10 sheets excluding 0 and negative values: =SUMPRODUCT(SUMIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,10}&"'!C26"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,9,10}&"'!C26"),"0")) You can shorten the formula a little by using a defined name. InsertNameDefine Name: Sheets Refers to: =10*{1,2,3,4,5,6,7,8,9,10} Then: =SUMPRODUCT(SUMIF(INDIRECT("'am peak_"&Sheets&"'!C26"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'am peak_"&Sheets&"'!C26"),"0")) -- Biff Microsoft Excel MVP "Sowmya C" wrote in message ... Yes, that's right. I tried to work around with the solution previously provided, for which i renamed the sheet as 'am peak_1', 'am peak_2' and so on... as it seems to not recognize the increment of 10 (ie. 10, 20...). And it worked (with one particular cell location though)! But as the cell location is a text in the above stated formula, I am not sure how I can apply that to a group of cells. As in my case, I am looking to apply the conditional average to alternate columns. In one of the other threads under the same topic, the suggestion was to use the IF statement and create another table, replacing <0 values by "" and then calculate average. Though the solution is pretty simple, I am hoping there would be a 'one formula' solution to this problem, as I need to extract such averages for 18 scenarios (ie. 18 different excel sheets)! Thanks, Som "T. Valko" wrote: average ... the same cell across 10 sheets sheets are labeled 'am peak_10' to 'am peak_100' ignoring the value if it is <=0 If you have 10 sheets named "10" to "100" then I'm assuming the names increment by 10? 'am peak_10' 'am peak_20' 'am peak_30' 'am peak_40' etc etc 'am peak_100' -- Biff Microsoft Excel MVP "Sowmya C" <Sowmya wrote in message ... What if I have to take the average of the values located in the same cell across 10 sheets (ignoring the value if it is <=0). For instance, the sheets are labeled 'am peak_10' to 'am peak_100' and the cell location is C26. I am not quite familiar with the 'indirect' function hence not able to comprehend the above solution. Any help would be appreciated. Thanks! "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "JimG" wrote in message ... AWESOME Solution! I would never had thought to try that. Thank You very much! "T. Valko" wrote: Conditional calculations across sheets isn't easy. Try this: =SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0")) -- Biff Microsoft Excel MVP "JimG" wrote in message ... I have an AVERAGE IF formula that works fine on a single worksheet for July 1st... =AVERAGEIF('7-1'!N:N,"0",'7-1'!N:N) I need it to work across 31 worksheets, so it will accurately calculate the averages for all values greater than zero, and for the entire month. Any ideas? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Sowmya C" wrote in message ... Wow...Thanks! That's really helpful and it surely works! And I just changed the formula a little bit to save some labor instead of changing the cell location in each and every formula. I created a separate table which had 'C, blank, E, blank,..., Y' in the first row and '26,27,28,29,30' in each column. And the modified equation I used is: =SUMPRODUCT(SUMIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,9,10}&"'!"&C$50&C51)," 0"))/SUMPRODUCT(COUNTIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,9,10}&"'!"&C$50&C51)," 0")) {for instance, here C50 has value 'C' and C51 has value '26'} And this I could copy across the rows and columns. Thanks a lot again for you help. Really appreciate it. All I had to do was create one averaging sheet and copy that for the other 18 scenarios! It made my job so much easier! :) Som "T. Valko" wrote: I am looking to apply the conditional average to alternate columns. Hmmm... That sounds like trouble! This formula will average cell C26 across your 10 sheets excluding 0 and negative values: =SUMPRODUCT(SUMIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,10}&"'!C26"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'am peak_"&10*{1,2,3,4,5,6,7,8,9,10}&"'!C26"),"0")) You can shorten the formula a little by using a defined name. InsertNameDefine Name: Sheets Refers to: =10*{1,2,3,4,5,6,7,8,9,10} Then: =SUMPRODUCT(SUMIF(INDIRECT("'am peak_"&Sheets&"'!C26"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'am peak_"&Sheets&"'!C26"),"0")) -- Biff Microsoft Excel MVP "Sowmya C" wrote in message ... Yes, that's right. I tried to work around with the solution previously provided, for which i renamed the sheet as 'am peak_1', 'am peak_2' and so on... as it seems to not recognize the increment of 10 (ie. 10, 20...). And it worked (with one particular cell location though)! But as the cell location is a text in the above stated formula, I am not sure how I can apply that to a group of cells. As in my case, I am looking to apply the conditional average to alternate columns. In one of the other threads under the same topic, the suggestion was to use the IF statement and create another table, replacing <0 values by "" and then calculate average. Though the solution is pretty simple, I am hoping there would be a 'one formula' solution to this problem, as I need to extract such averages for 18 scenarios (ie. 18 different excel sheets)! Thanks, Som "T. Valko" wrote: average ... the same cell across 10 sheets sheets are labeled 'am peak_10' to 'am peak_100' ignoring the value if it is <=0 If you have 10 sheets named "10" to "100" then I'm assuming the names increment by 10? 'am peak_10' 'am peak_20' 'am peak_30' 'am peak_40' etc etc 'am peak_100' -- Biff Microsoft Excel MVP "Sowmya C" <Sowmya wrote in message ... What if I have to take the average of the values located in the same cell across 10 sheets (ignoring the value if it is <=0). For instance, the sheets are labeled 'am peak_10' to 'am peak_100' and the cell location is C26. I am not quite familiar with the 'indirect' function hence not able to comprehend the above solution. Any help would be appreciated. Thanks! "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "JimG" wrote in message ... AWESOME Solution! I would never had thought to try that. Thank You very much! "T. Valko" wrote: Conditional calculations across sheets isn't easy. Try this: =SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0"))/SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!N:N"),"0")) -- Biff Microsoft Excel MVP "JimG" wrote in message ... I have an AVERAGE IF formula that works fine on a single worksheet for July 1st... =AVERAGEIF('7-1'!N:N,"0",'7-1'!N:N) I need it to work across 31 worksheets, so it will accurately calculate the averages for all values greater than zero, and for the entire month. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AverageIf | Excel Discussion (Misc queries) | |||
Averageif help | Excel Discussion (Misc queries) | |||
AverageIF | Excel Discussion (Misc queries) | |||
averageif | Excel Worksheet Functions | |||
averageif | Excel Discussion (Misc queries) |