If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average of Years employed
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" whe 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 |
#2
|
|||
|
|||
Average of Years employed
1. =e4-d4 gives you the number of days the person has been on staff.
2. Copy this down for all staff members 3. Average this range. 4. If you want to display the result the same way, you can use: =DATEDIF(0,a1,"y") & " Yrs, " & DATEDIF(0,a1,"ym") & " Months" where a1 holds the average. Regards, Fred "SGee" wrote in message ... 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" whe 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 |
#3
|
|||
|
|||
Average of Years employed
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 "SGee" wrote in message ... 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" whe 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 |
#4
|
|||
|
|||
Average of Years employed
How about doing the averages first:
=datedif(average(d4:d999),average(e4:e999),"y") & " Years, " & ..... Change the ranges to match. SGee wrote: 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" whe 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 -- Dave Peterson |
#5
|
|||
|
|||
Average of Years employed
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" whe 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.) |
#6
|
|||
|
|||
Average of Years employed
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. -- Biff Microsoft Excel MVP "SGee" wrote in message ... 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" whe 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 |
Thread Tools | |
Display Modes | |
|
|