View Single Post
#3
Posted to microsoft.public.excel.worksheet.functions
 Gary''s Student external usenet poster Posts: 11,058
Calculating Average Length of Service

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

=SUMPRODUCT(--(A1:A100="M"),--(B1:B10020),--(B1:B100<31),C1:C100)/SUMPRODUCT(--(A1:A100="M"),--(B1:B10020),--(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

--
Gary''s Student - gsnu2007k

"EasyPeasy" wrote:

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