using match, lookup, index

G

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?
 
F

Frank Kabel

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

Alan Beban

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

array entered.

Alan Beban
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top