Prev: Vlookup, COUNTIF, IF or Nested
Next: Showing 0 after decimal with ROUND
From: Joe User on 28 Nov 2009 18:33
"T. Valko" <biffinpitt(a)comcast.net> wrote:
> Can't use AND in this application.

Thanks. My bad! For i = 1 to 10000: Debug.Print "I will always test my
solutions": Next.

I should have written:

=AVERAGE(IF((MONTH(A1:A6)=10)*(B1:B6="reported")*ISNUMBER(SEARCH("john",C1:C6)),
D1:D6))

Again, that's an array formula. See the notes in my original posting.

Also note that this assumes that A1:A6 contains actual dates (serial
numbers), formatted as d/mm/yyyy.


JRD wrote:
> the answer for the example would be 4+2
> divided by 2 = 3

I don't think so. The only lines that meet all 3 conditions are lines 4 and
6. The average is (2+2)/2 = 2.


----- original message -----

"T. Valko" <biffinpitt(a)comcast.net> wrote in message
news:uEDaxsFcKHA.2188(a)TK2MSFTNGP04.phx.gbl...
> >=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
>>>
>>>
>>
>
>

First  |  Prev  | 
Pages: 1 2
Prev: Vlookup, COUNTIF, IF or Nested
Next: Showing 0 after decimal with ROUND