Pivot Table Calculations

Posted on 2011-04-28
Last Modified: 2012-05-11

I have an XLS attached where I want to perform some additional calculations in the pivot table. The Data tab shows the data which is a 6 week history of tickets/calls over this period.

The Pivot Table sheet shows the pivot table I have created showing the number of tickets/calls received per hour for each day and week.

The calculations I want to perform are around averages. I want to add the following:

- Show average number of tickets per week by hour of the day with total average for week (ideally a summary row at the bottom of the pivot table)

- Show average number of tickets per same weekday (Mondays, Tuesdays etc) by hour of the day with total average for whole day (again a summary at the bottom)

An example is show in yellow at the bottom of the pivot table.

Can these calculations be shown in the same pivot table or do they need to be in a separate one?


Question by:inforr
    LVL 41

    Accepted Solution

    Working with averages is difficult, as the dataset may not support everything one would like to do without helper columns, etc., in the data.  Even then, getting the pivottable to do EVERYTHING one wants, is sometimes not possible.

    You can click on the Day field and insert the wk Avg calculated field (re: (Mon+Tue+Wed+Thu+Fri+Sat)/6 and get an average, butI don't believe you can create at the same time have your summary (re: Grand total info, etc.) information work correctly.

    If all you want is a summary, there's an easier way.  As you have Excel 2007+, let's avail ourselves of the array function COUNTIFS to create the table you desire.

    See attached,


    Author Comment

    Hi Dave,

    Thanks for your response.

    The COUNTIFS function is useful in this case as it gives me the totals as you describe from which I can derive the averages. I have a few follow up questions:

    - It is possible to build this into the pivot table using a calculated field?
    - What is the purpose of the table in yellow?

    LVL 41

    Expert Comment

    The table in yellow was your table at the bottom of the sheet in your original question - there for an example for me and I didn't delete it.

    I don't believe this can be done in a calculated field the way everything is set up and while you can do the actual calculated ITEM for what you want, you can't get it formatted the way you want at the same time, lol.

    Recall me comment >> You can click on the Day field and insert the wk Avg calculated field (re: (Mon+Tue+Wed+Thu+Fri+Sat)/6 and get an average


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now