View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Teethless mama Teethless mama is offline
external usenet poster
 
Posts: 3,718
Default Calculating Average Length of Service

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


Earlier versions:

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


"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