Getting average from information in 2 separate pivot tables question

[Anonmau5]

Hello Excel Community


I have a problem I've been working on for about 4 hours a night for this past entire week and I can't figure it out. I would greatly appreciate any help that will lead me in the right direction to getting this solved so I can go back to sleeping.


The situation:
I have two pivot tables that pull data from two sheets in the workbook.


- The first pivot table "A" shows an average of the persons closed cases per day for each person.
- The second pivot table "B" shows the amount of hours the person worked each day.


The information in sheet 2 for pivot table "A" has a list of all the cases each person closed that day and the chart is set to count the total for the day and display it.


The information for pivot table "B" has a list of all the punch "in/out" times and displays the sum of those duration.


What I am trying to do is have a pivot table or a very low maintenance chart that will take the total cases closed for the person that day and divide it by the hours they worked and show their "average" for that day. I have tried to find a way to use the data from those pivot tables and the only way I have found was to manually add each persons data off the pivot tables to the manual chart. This is not an option as there is too much to track like this manually.


I also tried creating a pivot table using the information from the two sheets that provide the data for pivot table "A" & "B" however when I do that you loose all the unique field labels at the top in the data sheets and only have about 4 options to sort and work with on the pivot table bar. If I could get all the different options on the right to come up with it pulling from one sheet when I set it up for two I could probably make it work. Then it would just be a matter of listing both totals next to each other and doing a calculated field to divide the one by the other.


Again I greatly appreciate help from anybody that can help restore my sanity! :P


- Brian

 

[konew1]

There is no easy way because, as Jerry has pointed out, you have different names for the employess in different places. With 7 employees and expect to grow to 15, that is not a lot to manage.
Here is one suggestion, put a helper column in the two data extract ranges to generate a standard name for each employee.
Your system maintenance will involve copying both those formulas down each time you want to report.
The out put is to a formula driven table. Your maintenance will be to maintain the list of standard names, insert the dates each month, and copy the formula down and across. I made 2 reciprocal output tables, delete the one you don't want.

An variation on this approach is to maintain a separate table with the names as returned with closed cases and the employee number, and use vlookup to populate employee numbers into the data range. It will be more robust than this possibility.

Excel Workbook
	A	B	C	D	E	F	G
3	Case Type	open Date	Account #	Status	Close Date	Case Owner	Std Name
4	*	*	*	closed	1/01/2015	Bob Smith	Smith, B
5	*	*	*	closed	1/01/2015	John Doe	Doe, J
6	*	*	*	closed	2/01/2015	Bob Smith	Smith, B
7	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*
10	EmployeeID	Name	Date	In	Out	Total Hours	Std Name
11	doe, John J	*	1/01/2015	*	*	7	doe, J
12	doe, John J	*	2/01/2015	*	*	3	doe, J
13	Smith, Bob Walter	*	2/01/2015	*	*	10	Smith, B
14	Smith, Bob Walter	*	1/01/2015	*	*	5	Smith, B
15	*	*	*	*	*	*	*
16	Average Cases Closed per Hour	*	*	*	*	*	*
17	*	1/01/2015	2/01/2015	*	*	*	*
18	Smith, B	0.20	0.10	*	*	*	*
19	Doe, J	0.14	0.00	*	*	*	*
20	*	*	*	*	*	*	*
21	Average Cases Hours per Closed Case	*	*	*	*	*	*
22	*	1/01/2015	2/01/2015	*	*	*	*
23	Smith, B	5.00	10.00	*	*	*	*
24	Doe, J	7.00	*	*	*	*	*
Sheet1

I know you want low maintenance, but that is impossible when your data does not match

 

[Anonmau5]

Thank you for your responses. I should be able to work something out from what was posted here. The reason I was looking for "low maintenance" is that I don't actually manage the employees. I'm doing most of the reporting for our department in an effort to progress a little. So everything I do is in conjunction to my normal responsibilities.

Again I appreciate the time everybody took when responding.