Home > Forum Home > Presenting and Reporting Data > Summarize by Average in Pivot Shows #Div/0! Share

Summarize by Average in Pivot Shows #Div/0!

Excel Help for Summarize By Average In Pivot Shows #div/0! in Presenting and Reporting Data


Forum TopicPost Reply Login

Summarize By Average In Pivot Shows #div/0!

Rate this:
(3/5 from 1 vote)
ConfusedHello,
I have a helpdesk ticket call statistics pivot table that currently shows the hour of day along the vertical (column) axis and day of week on the horizontal (row) axis and correctly shows the counts of the calls for each hour\day of week cell in the body of the pivot table (I can send file if requested).
But instead of the counts, what I really need is to show the AVERAGE number of calls coming in for each hour\day of week, but when I try to change the 'Summarize by' option from Count to Average, ALL the cells in the body have a #DIV/0! in them? I would think this would happen for those hour\DoW combination that do not have any calls, but certainly not for ALL the cells?
I'm aware of the pivot table option of masking errors with other values, but this does me no good when the entire table is showing zero as it is in this case.  I tried adding a column of 1's in the base data for every row and then summarize the pivot by that new column as an average, but all it did was give me 1's in every cell of the pivot table.     
The data has a thousand or so rows having tickets spread over the entire year of 2010 at all hours of the day and day of week. A sampling of the data looks like:
Columns: TicketNo, OpenDateTime, DayOfWeek
Data:
123, 1/1/2010 1:10AM, Sunday
234, 1/1/2010 2:45PM, Monday
345, 1/5/2010 1:10AM, Wednesday
456, 1/7/2010 1:30AM, Sunday
...etc.
Any help or other approach to get the desired result would be greatly appreciated.
Cheers,
Maddog
 Posted by on
 
Replies - Displaying 1 to 3 of 3Order Replies By: Most recent | Chronological | Highest Rated
Oops
Creating a calculation table
Rate this:
(3/5 from 1 vote)
Assuming that your Pivot table is not so big (7 days by 24 hours), you could just go with the count by hour for each day of the week and then create a similar static table either underneath of in a separate sheet. 

The contents of the static table would be calculations for each hour and weekday intersection by dividing the total count for that combination by the total count for that day (or hour, depending on what average you want).
 Excel Business Forums Administrator
 Posted by on
Confused
Tried the Hour but no change, I have new idea possibly?
Rate this:
(3/5 from 1 vote)
Hi & thanks for responding.  On the second suggestion I added the Hour column by itself, and as I already had a count column with 1's in it from a prior attempt I then refreshed and modified the pivot to use the hour field on the vertical axis and kept the DayOfWeek field on the horizontal and used the average against the Count field as the value, but still only returned 1's in the pivot as before, so it didn't seem to matter in using the Hour field separately or the group by Hour against the OpenDateTime field in this case.
I am not sure how to go about your other suggeston of creating a calculation table using the body and totals of the pivot table.  
In trying to figure out why the pivot is showing a low average number of 1's, I changed the pivot to remove the hour side of it simply to get an average for tickets coming in on the days of the week only.  I then stuck an abnormally large test value in one of the Count data row fields in place of a 1 and then I DID see the corresponding average value in the Pivot change.  What I realized then was the pivot average calculation was simply adding up all the counts for the Mondays and then dividing it by the number of rows in the data that fit that criteria, and since they count values were all 1's the sum is always the same as the number of rows so the resulting average would ALWAYS be 1.  This does me no good obviously.  So that got me to thinking there needs to be another type of grouping in the raw data so that the count column values are not always 1, like grouping by week number maybe and getting a total count of tickets for each day of the week for all the weeks of the year first, and then calculate the average across those groupings?
 Posted by on
Surprised
Create Hour Field for the Report
Rate this:
(3/5 from 1 vote)
In this case, you really want to have the hour as a Pivot table field.  To do this, create an extra column in the source data and use the function HOUR on the OpenDateTime column.  You can then create the Pivot table with the Day of the Week on one axis and the Hour on the other with the Count of tickets as the value.

In order to get the average number of tickets per hour per day, you can either create a calculation table using the body and totals of the pivot table, or recreate the Pivot table with the value 1 against each ticket so that the average option can be chosen and not applied to the actual ticket reference number.
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Summarize by Average in Pivot Shows #Div/0! in the

© Business Spreadsheets   Legal   Follow us: