software

Hi everyone

Probably a simple one.

I want to find the average of column A for the cells who's row has a
value of 1 in BOTH columns B and C.

I can get to do if for only column A and B with:
{=AVERAGE(IF($B$1:$B$100=1,$A$1:$A$100))} but can't extend this to
also IF C1:100=1 as well.

Any suggestions. Thanks!!Hi!

Try this:

Array entered:

=AVERAGE(IF((B1:B100=1)*(C1:C100=1),A1:A100))

Biff

quot;vinnie123quot; gt; wrote in message ups.com...
gt; Hi everyone
gt;
gt; Probably a simple one.
gt;
gt; I want to find the average of column A for the cells who's row has a
gt; value of 1 in BOTH columns B and C.
gt;
gt; I can get to do if for only column A and B with:
gt; {=AVERAGE(IF($B$1:$B$100=1,$A$1:$A$100))} but can't extend this to
gt; also IF C1:100=1 as well.
gt;
gt; Any suggestions. Thanks!!
gt;
Try the following:

=sumproduct(($a$1:$a$100)*($b$1:$b$100=1)*($c$1:$c $100=1))/sumproduct(($b$1:$b$100=1)*($c$1:$c$100=1))

For more info on SUMPRODUCT, see this page (helped me lots!!!)

www.xldynamic.com/source/xld.SUMPRODUCT.html

Félix

quot;vinnie123quot; wrote:

gt; Hi everyone
gt;
gt; Probably a simple one.
gt;
gt; I want to find the average of column A for the cells who's row has a
gt; value of 1 in BOTH columns B and C.
gt;
gt; I can get to do if for only column A and B with:
gt; {=AVERAGE(IF($B$1:$B$100=1,$A$1:$A$100))} but can't extend this to
gt; also IF C1:100=1 as well.
gt;
gt; Any suggestions. Thanks!!
gt;
gt;