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

Forum Topic | Post Reply Login |

## Summarize By Average In Pivot Shows #div/0! | Rate this: (3/5 from 1 vote) |

Hello, 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 Maddogs on |

Replies - Displaying 1 to 3 of 3 | Order Replies By: Most recent | Chronological | Highest Rated |

Rate this: (3/5 from 1 vote) 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 Excel Helper on |

Rate this: (3/5 from 1 vote) 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 Maddogs on |

Rate this: (3/5 from 1 vote) 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 Excel Helper on |

Displaying page 1 of 1 |

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