Calculating Weekly Average by user given daily data input in excel pivot table

0
votes

So I started using Pivot Tables a few weeks ago, but I'm pretty decent at Excel otherwise. I hit an issue that should be an easy fix and I don't see it. I have a document that is tracking Key Performance Indicators for my warehouse packing department. I have 2 tabs in the document that matter (Input Log, and Analysis).

Input log is basically copied from a report generated from my warehouse system. Gives me a USER, DATE, HRS WORKED, ORDERS PACKED, ITEMS PACKED. Using a pivot table I want to see the average hours worked by week for each user.

Currently I can only see the Sum of the hours works and the daily average. How do I also see the weekly average?

pivotTable

[InputLogData][2]

I was able to get the workbook hosted on google drive Packing KPI Workbook

1
excelpivot-tablekpi
In the Value field settings, Average is one of the options. Then just group your data by weeks (or by days = 7). Or if you are using the WE column, you can just drag that to the rows area, along with the User column. Your screen shot is not sufficient to allow me to provide more information, so, if that is not enough, then provide useable data. – Ron Rosenfeld
Thanks for the quick response. I was able to upload a copy of the workbook to google drive. – Daniel Gurzi
I don't want to group the data by weeks, What I want to see is the data grouped by users, but I want to see the average number of hours that each user works in a week. Being that they could work anywhere from 4 to 6 days a week, I can't multiply the daily by 5, as the number wouldn't be right. I can figure out how to do it without the pivot table, but it isn't as easy to manipulate date ranges that way. And I can't use Sumifs in a pivot table, that I know of. it might not be possible to do what I want, but I'm hopeful. – Daniel Gurzi

1 Answers

1
votes

If you create the pivot table, but select to "add to data model",

enter image description here

you will see, in the "Value Fields Settings" selection, a Distinct Count item.

enter image description here

You can then do a Distinct Count of the WE column, and add a column to divide the total hours worked by User, by the total number of Weeks. (See the formula in D4: =B4/C4 )

enter image description here

EDIT: If you want to have the results within the Pivot Table itself, you can add a few calculated columns to your input log.

WE per User is the Unique number of weeks each user works. This formula is an array formula and must be entered by holding down ctrl + shift while hitting enter. Excel will place braces {...} around the formula seen in the formula bar:

=SUM(--(FREQUENCY(IF(User=D2,WE),WE)>0))

Hrs Worked This Wk is the hours worked in the week by the user. This is not really needed, but is in there for when I was troubleshooting the calculations.

=SUMIFS(Hrs_Worked,WE,A2,User,D2)

This will be used to calculate the average for the AvgHrs/Wk column:

=SUMPRODUCT((User='Input Log'!$D2)*Hrs_Worked/WE_Per_User)

enter image description here

This can then be used to create the Pivot Table:

enter image description here

In the Pivot Table, the

  • Total Hrs is the SUM of Hrs Worked
  • Hrs-Daily is the Average of Hrs Worked,
  • Weekly Hrs is the Average of AvgHrs/Wk