Determine YTD Average Employee Weekly Hours

Hi,

I have a log file that has employees weekly hours between customer billable (Comp. A) & non-billable customer (Comp. X).

I would like to establish a YTD (Year to Date) weekly hours average to validate that employees are working towards the target hours. Once calculated, I will chart this for the team.

As you will see with attached file, I have 2 billable hours as follows:
* Company A
* Company X

I can breakdown & calculate the total hours between each easy enough. Now, I’m seeking a rolling function that will provide the weekly average between 40 to 60 hours per week from the individual.
Please assist around Cell BH

Thank you.

If you can change you sheet layout, here is how you should go. It is more like a database table and you can use a pivot table to do the stats and graphs for you.
Look at attached file.

Hi p24leclerc & Others,

Thanks for the feedback.

I have a team of 20 Designers. I can go for your proposed layout, but I'm not seeing how I would chart this cummulatively. Meaning after several weeks, I should see if a person is averaging 40 hrs. per week or 55 hrs. per week (max hours) in a chart format, which is ultimately what I'm trying to set up.


I was thinking I can get the total hours & divide or average the number of weeks, but the calculations are either too low or high - Under 40 or over 60. Perhaps I'm calculating wrong. For example, one Designer has accumlated 280.20 hours for the last several weeks or Fiscal Week 7, which calculates approximately 40 hours. However, I know this particular individual is averaging 45 to 55 hours so at the moment, I'm not trusting the results.

How can I get this to calculate the average over current number of weeks & be able to chart it with yours or another suggested format?

Thanks

see attached file for an example of a pivot graph.
Pivot table and Pivot graph results can be changed by right clicking on the column title and selection field value settings. You can choose either Sum Average or else.

Hi p24leclerc,

The use of Pivot Table & Graph is a much different approach than I planned. Although I see some of its benefits, is it possible to get the sum of total hours over specified weekly duration & getting a rolling 40 to 60 work week average through some type of function?

At least to me, I think that's a simplier format in which I would graph out the average weekly hour columns.

Thanks

Do you want to keep your original layout or can you change to the one I'm proposing?
I think it would be easier with my layout.

Can the error in the average come from the fact that you have 0 or empty cells in the row? Is this possible to have 0 or an empty cell in Billing?

You can use a function to count how many weeks have a number greater than 0 in that row and use that number to devide the total of hours billed.

Please Login or Register  to view this content.

Hi p24leclerc,


I'm seekinng a function that can provide the average weekly hours - perhaps, based fro the current Fiscal Week, such as FW 7 that was last week.

Thanks

Look at the formula for the average weekly hours in the attached file.
Is it close to waht you want. Having only one row of data, I can't be sure it works well.
Please give it a try.

Hi p24leclerc,

Yes - I'm seeking the average weekly hours. Your function appears to be giving me the desired results but with some individual instances, the average hours is less than 40 hrs. per week, which kind of surprises me.

I think this may be because the 1st week of the year was a short week & perhaps lessoning the average.

Thanks for the info. I think I have what I need now but still open to suggestions.

Without more data to work with, it will be hard for me to go any further.
Regards

Hi,

Please see attached example.

As you see in Column CM, I have following function, going down the table:

=CL9/(COUNTIFS($I$8:$CH$8,"*Total*",I9:CH9,">0"))


For example, Employee 26 has been averating 50+ hours per week & yet the result is showing 43 cumlative or Billable / Non-Billabe hours, which appears to be a low result.

Please advise....

Thanks

I don't see any problem with the formula.
For employee 26, I noted the hours reported in your sheet and here is what I found:
30 - 58 - 2 - 55 - 60 - 0 - 55 hours totals
This gives you a total of 260 hours like you have in your table
It also gives you 6 weeks of reported work to divide this total for a result of 43.33 hours/week

Nothing is wrong there.
Maybe the data are wrong but the calculation seems OK.
Regards

Hi p24leclerc,


Yes - it appears that the calculaitons or the function results are correct. Just a little surprised that some results seems low.

Thanks for all the help.