using match, lookup, index

[Gautam Sethi]

i am wondering if there is a clean way to do this: i have data in
column A on a variable which could be either 0 (male) or 1 (female).
column b has other data. each row corresponds to a survey respondent.
i want to use functions like AVERAGE, STDEVP etc on the b column for
males and females. for example:

GENDER AGE
0 24
1 34
1 44
0 26

the average age of men in this sample is 25, and that of women is 39.
is there an easy/simple way to do this?

 

[Frank Kabel]

Hi
try
=AVERAGE(IF(A1:A100=0,B1:B100))
and enter this formula as array formula wirh CTRL+SHIFT+ENTER

 

[Alan Beban]

=AVERAGE(IF(INDIRECT("A1:A"&COUNTA(A:A))=0,INDIRECT("B1:B"&COUNTA(A:A))))

array entered.

Alan Beban