Prev: how to convert time to decimal format
Next: Can a pivot table be redirected to an external source?
From: Doug on 14 May 2010 12:34
I have reference data in Column "$Q$3:$Q$502" of "sheet1". For all cells in
the reference column that contain "yes", I would like to average quantity in
cells for Column $S$3:$S$502 respectively.

So if it finds 3 in column Q with "Yes" (2,3,4) , it will return the number 3.
--
Thank you!
From: Eduardo on 14 May 2010 12:38
Hi,

=AVERAGEIF($Q$3:$Q$502,"YES",$S$3:$S$502)

"Doug" wrote:

> I have reference data in Column "$Q$3:$Q$502" of "sheet1". For all cells in
> the reference column that contain "yes", I would like to average quantity in
> cells for Column $S$3:$S$502 respectively.
>
> So if it finds 3 in column Q with "Yes" (2,3,4) , it will return the number 3.
> --
> Thank you!
From: Luke M on 14 May 2010 13:07
for pre-2007:
=SUMIF(Q3:Q502,"Yes",S3:S502)/COUNTIF(Q3:Q502,"Yes")

--
Best Regards,

Luke M
"Eduardo" <Eduardo(a)discussions.microsoft.com> wrote in message
news:3ECB3413-5FD5-4F28-842E-AC5D44DB6FDB(a)microsoft.com...
> Hi,
>
> =AVERAGEIF($Q$3:$Q$502,"YES",$S$3:$S$502)
>
> "Doug" wrote:
>
>> I have reference data in Column "$Q$3:$Q$502" of "sheet1". For all cells
>> in
>> the reference column that contain "yes", I would like to average quantity
>> in
>> cells for Column $S$3:$S$502 respectively.
>>
>> So if it finds 3 in column Q with "Yes" (2,3,4) , it will return the
>> number 3.
>> --
>> Thank you!


From: Chip Pearson on 14 May 2010 20:29
Try

=AVERAGE(IF(Q3:Q502="yes",S3:S502,FALSE))

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Fri, 14 May 2010 09:34:01 -0700, Doug
<Doug(a)discussions.microsoft.com> wrote:

>I have reference data in Column "$Q$3:$Q$502" of "sheet1". For all cells in
>the reference column that contain "yes", I would like to average quantity in
>cells for Column $S$3:$S$502 respectively.
>
>So if it finds 3 in column Q with "Yes" (2,3,4) , it will return the number 3.
 | 
Pages: 1
Prev: how to convert time to decimal format
Next: Can a pivot table be redirected to an external source?