average with conditions

Hi everyone. I've a spreadsheet with one column (eg. A1:A100)
containing a range of values (ages) and another column (B1:B100) a
value of either 1 or 2 (representing males vs females). How can a find
the average of the cells A1:A100 that have a value of 1 in column B;
ie. the average age of the males only

Any suggestions? Thanks.

=average(if(b1:b100)=1,a1:a100))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

vinnie123 wrote:
>
> Hi everyone. I've a spreadsheet with one column (eg. A1:A100)
> containing a range of values (ages) and another column (B1:B100) a
> value of either 1 or 2 (representing males vs females). How can a find
> the average of the cells A1:A100 that have a value of 1 in column B;
> ie. the average age of the males only
>
> Any suggestions? Thanks.

--

Dave Peterson

And you have more responses at your other post.

vinnie123 wrote:
>
> Hi everyone. I've a spreadsheet with one column (eg. A1:A100)
> containing a range of values (ages) and another column (B1:B100) a
> value of either 1 or 2 (representing males vs females). How can a find
> the average of the cells A1:A100 that have a value of 1 in column B;
> ie. the average age of the males only
>
> Any suggestions? Thanks.

--

Dave Peterson

Hi,
is there anyone who can tell me how to solve the same problem but with 2
conditions.. i want the average of say column F, when 2 conditions are
fullfilled, in column A and C.
I tried
=AVERAGE(IF((A2:A22="X")+(C2:C22="Y"),F2:F22))
with ctrl shift enter, but i cant make it work...

thanks,
S
"Dave Peterson" wrote:

> =average(if(b1:b100)=1,a1:a100))
>
> This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> correctly, excel will wrap curly brackets {} around your formula. (don't type
> them yourself.)
>
> Adjust the range to match--but you can't use the whole column.
>
> vinnie123 wrote:
> >
> > Hi everyone. I've a spreadsheet with one column (eg. A1:A100)
> > containing a range of values (ages) and another column (B1:B100) a
> > value of either 1 or 2 (representing males vs females). How can a find
> > the average of the cells A1:A100 that have a value of 1 in column B;
> > ie. the average age of the males only
> >
> > Any suggestions? Thanks.
>
> --
>
> Dave Peterson
>