Excel Formula for Avg Tenure with a twist

[mminchev]

Hi I am trying to figure out a formula that would give me the average tenure per year of the Active employees for that year. I have a list of the employees with start and end dates and their Departments, and their tenure for each year they worked.

Here are the two lists: What I need is to get the Avg Tenure per year and Department based on the tenure under each year in the list to the right.

12/31/1995

12/31/1996

12/31/1997

12/31/1998

Year

Start of Year

End of Year

Dept

Avg Tenure

Name

Start Date

End Date

Term Year

Term Adjust

Dept

1995

1996

1997

1998

1997

1/1/1997

12/31/1997

Sales

Bob

1/8/1995

5/8/1997

1997

5/8/1997

Sales

1.0

2.0

2.3

NA

1997

1/1/1997

12/31/1997

HR

Lisa

1/8/1997

9/8/1998

1998

9/8/1998

Accounting

NA

NA

1.0

1.7

1997

1/1/1997

12/31/1997

Accounting

Jon

9/7/1997

2022

5/17/2022

HR

NA

NA

0.3

1.3

1997

1/1/1997

12/31/1997

Finance

Ingrid

4/8/1995

2/2/2000

2000

2/2/2000

Merchandising

0.7

1.7

2.7

3.7

1997

1/1/1997

12/31/1997

Merchandising

Vlad

1/1/1997

3/15/1998

1998

3/15/1998

Real Estate

NA

NA

1.0

1.2

1997

1/1/1997

12/31/1997

Real Estate

Jessica

9/8/1997

2022

5/17/2022

Sales

NA

NA

0.3

1.3

1998

1/1/1998

12/31/1998

Sales

Loui

11/8/1997

12/8/1998

1998

12/8/1998

Sales

NA

NA

0.1

1.1

1998

1/1/1998

12/31/1998

HR

Jess

12/10/1997

2022

5/17/2022

Finance

NA

NA

0.1

1.1

1998

1/1/1998

12/31/1998

Accounting

Nora

9/11/1998

2022

5/17/2022

Accounting

NA

NA

NA

0.3

1998

1/1/1998

12/31/1998

Finance

Mike

5/8/1997

12/31/1998

1998

12/31/1998

Merchandising

NA

NA

0.6

1.6

1998

1/1/1998

12/31/1998

Merchandising

Miles

4/21/1997

6/9/1998

1998

6/9/1998

Sales

NA

NA

0.7

1.1

1998

1/1/1998

12/31/1998

Real Estate

​

Gordon

1/2/1997

2022

5/17/2022

HR

NA

NA

1.0

2.0

Joey

6/7/1997

11/8/1998

1998

11/8/1998

Real Estate

NA

NA

0.6

1.4

Linda

2/9/1998

2022

5/17/2022

Finance

NA

NA

NA

0.9

Barb

3/3/1997

3/3/1998

1998

3/3/1998

HR

NA

NA

0.8

1.0​

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=AVERAGEIFS(INDEX($P$3:$S$17,,MATCH(YEAR(B3),$P$2:$S$2,0)),$O$3:$O$17,D3)

 

[Gerald Higgins]

Based on the data provided, should the answer for Sales, 1997, be 0.85 ? And for HR, same year, 0.7 ?

 

[mminchev]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=AVERAGEIFS(INDEX($P$3:$S$17,,MATCH(YEAR(B3),$P$2:$S$2,0)),$O$3:$O$17,D3)

This worked perfect!!!!!!!!!!!!

 

[Fluff]

You're welcome & thanks for the feedback.