Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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:D))/365.25) & " Yrs, " & ROUND(12*MOD((AVERAGE(E:E)-AVERAGE(D: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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Years employed
Try this array formula** :
D4:D20 = beginning employment date E4:E20 = promotion date =AVERAGE((E4:E20-D4:D20)/365.25) You may want to round off the result** : =ROUND(AVERAGE((E4:E20-D4:D20)/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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Best average for 5 years out of 8 | New Users to Excel | |||
Average between dates considering leap years. | Excel Discussion (Misc queries) | |||
What template is good for self employed gst calculations? | Excel Discussion (Misc queries) | |||
Calculate number of years and months employed using hire date and. | Excel Worksheet Functions | |||
Calculate Years/Months Between Dates and then Average | Excel Discussion (Misc queries) |