Can I use AVERAGEIFS?
in [Functions]
|
From: JRD on 28 Nov 2009 06:37 > Example: > >> > A B C D > >> > 1 01/10/2009 Cancelled John, Steven 4 > >> > 2 01/09/2009 Reported John, Steven 2 > >> > 3 20/10/2009 Reported John, Darren N/A > >> > 4 12/10/2009 Reported John, Darren, Steven 2 > >> > 5 14/10/2009 Reported Darren, Steven 4 >>>>>6 15/10/2009 Reported John, Darren 2 > > How can I average the numbers in column D, but only the ones in rows where A = October, B = reported and C contains "John"? (contains John, doesn't have to be exactly John). Note that there are some text strings in column D, so it is not all numbers - the answer for the example would be 4+2 divided by 2 = 3 > > Thanks
From: Jacob Skaria on 28 Nov 2009 06:57 Try (in 2007) =AVERAGEIFS(D1:D10,A1:A10,">=" & DATE(2009,10,1),A1:A10, "<" & DATE(2009,11,1),B1:B10,"Reported",C1:C10,"*John*") array formula which will work for 2003/2007 =AVERAGE(IF(TEXT(A1:A10,"mmyyyy")="102009",IF(B1:B10="Reported", IF(ISNUMBER(SEARCH("John",C1:C10)),IF(ISNUMBER(D1:D10),D1:D10))))) If this post helps click Yes --------------- Jacob Skaria "JRD" wrote: > > Example: > > >> > A B C D > > >> > 1 01/10/2009 Cancelled John, Steven 4 > > >> > 2 01/09/2009 Reported John, Steven 2 > > >> > 3 20/10/2009 Reported John, Darren N/A > > >> > 4 12/10/2009 Reported John, Darren, Steven 2 > > >> > 5 14/10/2009 Reported Darren, Steven 4 > >>>>>6 15/10/2009 Reported John, Darren 2 > > > How can I average the numbers in column D, but only the ones in rows where A = October, B = reported and C contains "John"? (contains John, doesn't have to be exactly John). Note that there are some text strings in column D, so it is not all numbers - the answer for the example would be 4+2 divided by 2 = 3 > > > > Thanks > >
From: JoeU2004 on 28 Nov 2009 09:50 "JRD" <JRD(a)discussions.microsoft.com> wrote: > How can I average the numbers in column D, > but only the ones in rows where A = October, > B = reported and C contains "John"? The following array formula: =AVERAGE(IF(AND(MONTH(A1:A6)=10, B1:B6="reported", ISNUMBER(SEARCH("john",C1:C6))), D1:D6) Note: An array formula is committed by pressing ctrl+shift+Enter instead of just Enter. The entire formula will be enclosed in curly brackets, i.e. {=formula}. If you make a mistake, press F2 to edit, then press ctrl+shift+Enter. > Note that there are some text strings in column D, > so it is not all numbers AVERAGE will automatically ignore cells with text strings. But if D3 is actually the Excel error #NA instead of the string "NA", you will need another term to ignore it. Namely: =AVERAGE(IF(AND(MONTH(A1:A6)=10, B1:B6="reported", ISNUMBER(SEARCH("john",C1:C6)), ISNUMBER(D1:D6)), D1:D6) ----- original message ----- "JRD" <JRD(a)discussions.microsoft.com> wrote in message news:ECCCA3AB-4D0C-4B1C-B245-029E9C6411D2(a)microsoft.com... >> Example: >> >> > A B >> >> > C D >> >> > 1 01/10/2009 Cancelled John, Steven >> >> > 4 >> >> > 2 01/09/2009 Reported John, Steven >> >> > 2 >> >> > 3 20/10/2009 Reported John, Darren >> >> > N/A >> >> > 4 12/10/2009 Reported John, Darren, Steven >> >> > 2 >> >> > 5 14/10/2009 Reported Darren, Steven >> >> > 4 >>>>>>6 15/10/2009 Reported John, Darren >>>>>>2 >> > How can I average the numbers in column D, but only the ones in rows >> > where A = October, B = reported and C contains "John"? (contains John, >> > doesn't have to be exactly John). Note that there are some text strings >> > in column D, so it is not all numbers - the answer for the example >> > would be 4+2 divided by 2 = 3 > > > > Thanks > >
From: T. Valko on 28 Nov 2009 13:47 >=AVERAGE(IF(AND(MONTH(A1:A6)=10, B1:B6="reported", >ISNUMBER(SEARCH("john",C1:C6))), D1:D6) Can't use AND in this application. AND returns a single result where you need an array of results. -- Biff Microsoft Excel MVP "JoeU2004" <joeu2004> wrote in message news:eg1XmoDcKHA.1648(a)TK2MSFTNGP05.phx.gbl... > "JRD" <JRD(a)discussions.microsoft.com> wrote: >> How can I average the numbers in column D, >> but only the ones in rows where A = October, >> B = reported and C contains "John"? > > The following array formula: > > =AVERAGE(IF(AND(MONTH(A1:A6)=10, B1:B6="reported", > ISNUMBER(SEARCH("john",C1:C6))), D1:D6) > > Note: An array formula is committed by pressing ctrl+shift+Enter instead > of just Enter. The entire formula will be enclosed in curly brackets, > i.e. {=formula}. If you make a mistake, press F2 to edit, then press > ctrl+shift+Enter. > > >> Note that there are some text strings in column D, >> so it is not all numbers > > AVERAGE will automatically ignore cells with text strings. But if D3 is > actually the Excel error #NA instead of the string "NA", you will need > another term to ignore it. Namely: > > =AVERAGE(IF(AND(MONTH(A1:A6)=10, B1:B6="reported", > ISNUMBER(SEARCH("john",C1:C6)), ISNUMBER(D1:D6)), D1:D6) > > > ----- original message ----- > > "JRD" <JRD(a)discussions.microsoft.com> wrote in message > news:ECCCA3AB-4D0C-4B1C-B245-029E9C6411D2(a)microsoft.com... >>> Example: >>> >> > A B C D >>> >> > 1 01/10/2009 Cancelled John, Steven 4 >>> >> > 2 01/09/2009 Reported John, Steven 2 >>> >> > 3 20/10/2009 Reported John, Darren N/A >>> >> > 4 12/10/2009 Reported John, Darren, Steven 2 >>> >> > 5 14/10/2009 Reported Darren, Steven 4 >>>>>>>6 15/10/2009 Reported John, Darren 2 >>> > How can I average the numbers in column D, but only the ones in rows >>> > where A = October, B = reported and C contains "John"? (contains John, >>> > doesn't have to be exactly John). Note that there are some text >>> > strings in column D, so it is not all numbers - the answer for the >>> > example would be 4+2 divided by 2 = 3 >> >> > > Thanks >> >> >
From: Pete_UK on 28 Nov 2009 14:33 Perhaps you could use: =SUMIFS( ... ) / COUNTIFS( ... ) with the same conditional parameters in the two functions. (Sorry, I don't have XL2007, so am unsure of the syntax). Hope this helps. Pete On Nov 28, 11:37 am, JRD <J...(a)discussions.microsoft.com> wrote: > > Example: > > >> > A B C D > > >> > 1 01/10/2009 Cancelled John, Steven 4 > > >> > 2 01/09/2009 Reported John, Steven 2 > > >> > 3 20/10/2009 Reported John, Darren N/A > > >> > 4 12/10/2009 Reported John, Darren, Steven 2 > > >> > 5 14/10/2009 Reported Darren, Steven 4 > >>>>>6 15/10/2009 Reported John, Darren 2 > > > How can I average the numbers in column D, but only the ones in rows where A = October, B = reported and C contains "John"? (contains John, doesn't have to be exactly John). Note that there are some text strings in column D, so it is not all numbers - the answer for the example would be 4+2 divided by 2 = 3 > > > > Thanks
|
Next
|
Last
Pages: 1 2 Prev: Vlookup, COUNTIF, IF or Nested Next: Showing 0 after decimal with ROUND |