Calculating Average Length of Service

[EasyPeasy]

I've got a spreadsheet that includes 3 columns with range names of 'gender'
(containing either M or F), 'age' and 'company_service' (both expressed as
years).

I want to be able to calculate the average length of service for men and
women separately based on age bands (eg between 21 and 30).

Can someone please start me off with a formula that can handle one set of
conditions from which I can hopefully work out the rest. eg :

Lower Age Band 21
Upper Age Band 30
Gender M

Thanks

 

[Gary''s Student]

Divide the total years of service of the selected subgroup by the number of
individuals in the subgroup:

=SUMPRODUCT(--(A1:A100="M"),--(B1:B100>20),--(B1:B100<31),C1:C100)/SUMPRODUCT(--(A1:A100="M"),--(B1:B100>20),--(B1:B100<31))

for for data like:

F 38 3
F 32 4
F 38 7
F 31 5
F 27 6
F 32 3
M 40 5
M 38 3
M 30 1
M 20 5
F 23 6
F 26 2
M 27 6
F 30 4
M 27 4
M 37 4
F 20 7
F 23 2
F 38 1
M 22 6
M 30 4
F 20 4
M 29 1
F 33 5
M 30 3
F 29 4
F 23 4
F 39 7
M 34 6
F 25 1

for fomula returns 3.571428571

 

[Teethless mama]

XL-2007:
=AVERAGEIFS(C1:C100,A1:A100,"M",B1:B100,">20",B1:B100,"<31")
just press ENTER


Earlier versions:

=AVERAGE(IF((A1:A100="M")*(B1:B100>20)*(B1:B100<31),C1:C100))
ctrl+shift+enter, not just ENTER