Calculating Average Length of Service

E

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
 
G

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
 
T

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
 

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