AVERAGEIF
JimG
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?
T. Valko
=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" <Ji...@discussions.microsoft.com> wrote in message
news:C4C1DA47-3554-4B68...@microsoft.com...
JimG
Thank You very much!
T. Valko
--
Biff
Microsoft Excel MVP
"JimG" <Ji...@discussions.microsoft.com> wrote in message
news:82110CBC-DA8C-4AD7...@microsoft.com...
Ashish Mathur
Assuming that your data below is in range C6:C10, you can use the following
formula where B14 has 162.
=SUMPRODUCT((1*(LEFT(C6:C10,3))=B14)*(RIGHT(C6:C10,1)))/SUMPRODUCT(1*(1*(LEFT(C6:C10,3))=B14))
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"Tung Nguyen" <Tung Ngu...@discussions.microsoft.com> wrote in message
news:1BD6BEF4-091E-4E0F...@microsoft.com...
> I have a worksheet like:
> 162-1
> 162-2
> 162-3
> 163-1
> 163-2
> .........
> I 'd like to average the group of cells with the same first three digit? I
> am thinking about AVERAGEIF function but could not work out? Any help
> would
> appreciate?
> Thank you!
> Tung
Domenic
163, try...
D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:
=AVERAGE(IF(LEFT($A$2:$A$6,LEN(C2))=C2,$A$2:$A$6))
Hope this helps!
In article <1BD6BEF4-091E-4E0F...@microsoft.com>,
Domenic
=AVERAGE(IF(LEFT($A$2:$A$6,LEN(C2))+0=C2,RIGHT($A$2:$A$6,1)+0))
or
=AVERAGE(IF($A$2:$A$6<>"",IF(LEFT($A$2:$A$6,LEN(C2))+0=C2,RIGHT($A$2:$A$6
,1)+0)))
The latter allows for empty/blank cells. Note that both formulas need
to be confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
In article <someone-98F340...@msnews.microsoft.com>,
C@discussions.microsoft.com Sowmya C
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
>sheets are labeled 'am peak_10' to 'am peak_100'
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 C...@discussions.microsoft.com> wrote in message
news:282E36E0-1C70-4EDF...@microsoft.com...
Sowmya C
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
Sowmya C
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. :)
T. Valko
>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.
Insert>Name>Define
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" <Sow...@discussions.microsoft.com> wrote in message
news:227B1F9E-D1C2-411E...@microsoft.com...
Sowmya C
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
--
Biff
Microsoft Excel MVP
"Sowmya C" <Sow...@discussions.microsoft.com> wrote in message
news:9DFFE72A-32FB-4E7D...@microsoft.com...