Average number of employees per hour

**phiphika1453** · 04-20-2013, 09:58 PM

I can't come up with a good way to calculate or display this data.

I need to be able to report the average number of employees by Job Code (Column J) over a 6 month time frame.

I figured I would use 5 different charts for Monday thru Friday.

I think I want a multiple line graph with each line representing a job code. X axis to be hour of the day, y axis to average # of employees.

Thanks!!!

**dilipandey** · 04-21-2013, 04:23 AM

HI phiphika1453,

welcome to the forum.

See the attached file where I used the bottom average and the top time entries to prepare a line chart... if you need 6 months data, then replace the bottom average reference with 6 month's data average. thanks.

Admin and Outpt Clock Hours 11-4-12 to 4-13-13 without names.xls

Regards,
DILIPandey
<click on below * if this helps>

**phiphika1453** · 04-21-2013, 11:09 AM

Thanks for for the welcome and the reply.

The chart you built isn't exactly what i needed. Since there are more than 1 of each job code present throughout the day. The average should be higher than one.

I think I will have to use a COUNTIF function to get my avgerages per half hour. I was hoping to be able to do this in a pivot chart.

**dilipandey** · 04-21-2013, 02:59 PM

Since chart is an representation of the data hence we first need to create the data source for the chart... so can you provide manually what data you are looking for ? Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**phiphika1453** · 04-21-2013, 04:15 PM

I was able to get what I needed. I had to sum the number of occurences for each time slot then filtered by day of the week and diveded by the number of weeks in my data set to get the average number in each time slot.

I built a simple chart from this data.

Quick question though, is it possible to divide all numerical values in a pivot table by the same number?

Thanks