Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Chicago, Illinois, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Help Averaging (2002)

    I need to find the average age for a group of people based on a certain time frame, such as all employees hired in a certain year. Attached is a sample worksheet. Column A is the hire date, column B is the person's age. So say I need to find the average employee age for all employess hired in the year 2006. I've looked at other posts, tried many different formulas but just can't seem to figure this one out. I keep getting errors.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Help Averaging (2002)

    Enter the year (2006) in cell E1, for example.
    In another cell, enter the following array formula (confirm with Ctrl+Shift+Enter instead of just Enter):

    =AVERAGE(IF(YEAR(A2:A206)=E1,B2:B206))

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help Averaging (2002)

    I can't test because My Excel is currently out of order (thank you microsoft!), but I think this formula should help:

    =SUMPRODUCT(1*(YEAR(A1:A100)=2006),B1:B100)/SUMPRODUCT(1*(YEAR(A1:A100)=2006),(B1:B100<>0)*1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •