# Thread: Formula Help Averaging (2002)

1. ## 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.

2. ## 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. ## 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)

#### Posting Permissions

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