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

Question

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?

[InputLogData][2]

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

Answer 1

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

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

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 )

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)

This can then be used to create the Pivot Table:

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