Thread: Pivot Table Question

First off, I've attached a sample spreadsheet to illustrate the data I'm working with and the problem I'm encountering.

Basically, I have a report, generated through Cisco, that contains information about my company's call center. The report lists information in terms of Agent, date call was taken, time spent on the call, and who was calling (called a skillset in the report). This is all in the "data" tab of the attached.

What I'm trying to do is create a pivot table listing the total number of calls taken by each agent per day. I can get that to work fine, but what I also need to do is come up with an average calls per hours worked for each agent. Some of the agents (John and Peter in the example) work 8 hour days and some work 12.

This average is what I cannot get to work. Everything I've tried (custom formulas and whatnot) just give me errors or nonsensical answers. I hope this is an easy fix I'm just not getting. The "pivot table" tab shows how I'd like the table to look when it does work correctly.

Any and all help is appreciated, thanks!

sample.xls

If you can have the list of people who works 8 hours or 12 hours. We can have the analysic as your request.

Here's a spreadsheet with the agent shift hours as requested. Thanks!

sample2.xls

Hi Koalabob,

Try to put the agent in Row Labels field and in Values field as well , then change the value field settings to “Count”.

Please find attached

Regards