Selective Averages in Pivot Table

[Lecter]

Hi all,

I'm tearing my hair out over something that I hope you can shed some light on.

Until recently, I've been using a manual spreadsheet to produce daily stats relating to calls at work. Each day, I paste the previous day's stats into Page 1, which then produces reports and tables on the next 3 pages.

Someone recently sang to me the wonders of Pivot tables, and how I should be using them, allowing me to no longer have to manually intervene... so today, I decided to spend some time changing it.

The source data contains usernames, boolean activity codes (columns where if the record is 0, that thing didn't happen; if it's 1, that thing did happen), handling times, and finally outcomes (what happened at the end of each call).

My original file created a list of the day's activity, 30 instances of X, 10 instances of Y, and so on. From there, it broke each activity down into outcomes... for the 30 instances of X, 5 of those resulted in A; for the 10 instances of Y, 8 of those resulted in A, while 4 resulted in B... and so on.

I've managed to reproduce this in the pivot without too much hassle... until the last hurdle. The average handling time for each one. I need to reproduce the handling time for each specific call type. Previously, I used an array formula to SUMIF the handling times based on a condition, which I then divided by the number of records to give me the total average for that activity.

Thus far, I have failed miserably to reprduce this, and feel like I have hit a brick wall with the idea.

The table is happy to give me a straightforward list of all the handling times, making the table around 500 pages long... but seems unable to simply add or average them per activity.

Does anyone have any suggestions on how to go about doing this?

 

[Jerry Sullivan]

Hello and Welcome,

The instructions below assume you are using xl2007 and have Fields (Columns) in your source data that have the headings:

• Call Type
• Handling Times

The interface will be different if you have another version of Excel.
Or you might need to adjust if you have different data fields (for example Call Start Time, Call Finish Time instead of Handling Time).


Try this....

1. Display the Pivot Table Field List (if it isn't displayed already- Right Click on your Pivot Table then Show Field List).
2. Drag your Call Type field into the Row Labels section of the Field List
3. Drag your Handling Times Field into the Values section of the Field List. It might default to "Sum of Handling Times" or "Count of Handling Times"
4. Right Click on that item in the Row Labels section and click Value Field Settings.
5. In the Summarize value field by... section select Average from the list and click OK.

If you get stuck, just add another post describing what you tried and what happened.

Good luck!

 

[Lecter]

Hello and Welcome,



The instructions below assume you are using xl2007 and have Fields (Columns) in your source data that have the headings:

• Call Type
• Handling Times

The interface will be different if you have another version of Excel.
Or you might need to adjust if you have different data fields (for example Call Start Time, Call Finish Time instead of Handling Time).




Try this....

1. Display the Pivot Table Field List (if it isn't displayed already- Right Click on your Pivot Table then Show Field List).
2. Drag your Call Type field into the Row Labels section of the Field List
3. Drag your Handling Times Field into the Values section of the Field List. It might default to "Sum of Handling Times" or "Count of Handling Times"
4. Right Click on that item in the Row Labels section and click Value Field Settings.
5. In the Summarize value field by... section select Average from the list and click OK.

If you get stuck, just add another post describing what you tried and what happened.

Good luck!

Hi there,

Thanks for the response.

First thing... I'm on Office 2003. Groan, I know... we're a fan of the dark ages where I work. The data layout is slightly different to what you outline, as people can select more than one activity per call.

I've included a few screenshots to show some dummy data, where I am, and where I'm hoping to get to.

Here's a some dummy data from the database where each line is 1 call for someone:
http://imageshack.us/photo/my-images/803/pivot1.png/

Here's my ideal layout, absent the "AHT" average which would be at the end of each line. As can be seen, it gives total instances of each activity at the end, breaking it down to outcomes along the way. Missing is the AHT column at the end for the average handling time for each activity.
http://imageshack.us/photo/my-images/30/pivot3.jpg/

Here is where the suggestion took me:
http://imageshack.us/photo/my-images/14/pivot2.jpg/

I'm beginning to feel that Pivots are simply not the way forward, they seem to me to be a fairly rigid and inflexible tool unsuited to these calculations. My original plan was to somehow automate the import of the day's data into Page 1 through a similar styled function to the Pivot's dropdown page selectorm, where you can simply input a date and the spreadsheet will dump records matching that date.

I tried linking the sheet to the database as an external data source, unfortunately it seems to run a static query that never changes with no option to pop-up a user-friendly box requesting a date. I'm also aware that Excel freaks out if you import more than 65K records, which this database would exceed quite quickly if no filter was applied.

Any help in any direction would be greatly appreciated.

 

[Jerry Sullivan]

I haven't used PivotTables in xl2003 so I can't help you with the differences there.

Based on xl2007, you would be much better off if you can restructure your data like this:
PT Source

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 46px"><COL style="WIDTH: 74px"><COL style="WIDTH: 87px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Call ID</TD><TD>Activity</TD><TD>Outcome</TD><TD>AHT</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Activity 1</TD><TD style="TEXT-ALIGN: right">1111</TD><TD style="TEXT-ALIGN: right">1.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD>Activity 2</TD><TD style="TEXT-ALIGN: right">2222</TD><TD style="TEXT-ALIGN: right">1.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD>Activity 3</TD><TD style="TEXT-ALIGN: right">3333</TD><TD style="TEXT-ALIGN: right">1.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD>Activity 1</TD><TD style="TEXT-ALIGN: right">4444</TD><TD style="TEXT-ALIGN: right">1.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD>Activity 5</TD><TD style="TEXT-ALIGN: right">5555</TD><TD style="TEXT-ALIGN: right">1.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">6</TD><TD>Activity 6</TD><TD style="TEXT-ALIGN: right">6666</TD><TD style="TEXT-ALIGN: right">1.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">7</TD><TD>Activity 2</TD><TD style="TEXT-ALIGN: right">7777</TD><TD style="TEXT-ALIGN: right">1.00</TD></TR></TBODY></TABLE>
Excel tables to the web - Excel Jeanie Html 4

Regarding the 65K rows limitation of xl2003, yes that would appear to add to your challenges.

You might look into SQL querries as an alternative. I haven't used them much, but my understanding is they would give you the ability to filter your large dataset before it gets to the PivotTable Source data. If you are interested in learning more, search for posts by Fazza who is a master on the topic.