Average of Years employed

[SGee]

I am trying to find an average length of employment for a group of employees
at the time of their promotion. I am using the formula below to calculate
each employee's time in service at the date of promotion:

=DATEDIF(D4,E4,"y") & " Yrs, " & DATEDIF(D4,E4,"ym") & " Months"

where:
D4 = beginning employment date
E4 = promotion date

What I would like to do is average the time on the job for the employees
promoted in each group. I cannot simply average the column due (I think) to
the text in the output.

Any suggestions would be most appreciated. Thanks

Scott

 

[Rajesh Mehmi]

Hi Scott

could you not split your formula below
=DATEDIF(D4,E4,"y") & " Yrs, " & DATEDIF(D4,E4,"ym") & " Months"
in the next two columns

for years Column G
=DATEDIF(D4,E4,"y")
for months Column H
=DATEDIF(D4,E4,"ym")

then average them
=SUM(G4:G17)+(QUOTIENT(SUM(H4:H17),12))& " Yrs " & MOD(SUM(H4:H17),12) &"
Months"


--
Best regards

Rajesh Mehmi

(e-mail address removed)

 

[Dave Peterson]

How about doing the averages first:

=datedif(average(d4:d999),average(e4:e999),"y") & " Years, " & .....

Change the ranges to match.

 

[MyVeryOwnSelf]

I am trying to find an average length of employment for a group of

employees at the time of their promotion. I am using the formula
below to calculate each employee's time in service at the date of
promotion:

=DATEDIF(D4,E4,"y") & " Yrs, " & DATEDIF(D4,E4,"ym") & " Months"

where:
D4 = beginning employment date
E4 = promotion date

What I would like to do is average the time on the job for the
employees promoted in each group. I cannot simply average the column
due (I think) to the text in the output.

One way is to base the calculation on the original data, rather than on the
concatenation; for example:
=INT((AVERAGE(E:E)-AVERAGE(D))/365.25) & " Yrs, " &
ROUND(12*MOD((AVERAGE(E:E)-AVERAGE(D))/365.25,1),0) & " Months"

Explanation: the average of a bunch of differences equals the difference of
the averages.

Modify to suit.

(I use Excel 2003.)

 

[T. Valko]

Try this array formula** :

D420 = beginning employment date
E4:E20 = promotion date

=AVERAGE((E4:E20-D420)/365.25)

You may want to round off the result** :

=ROUND(AVERAGE((E4:E20-D420)/365.25),1)

That'll round to 1 decimal place

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.