[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Pivot Table Calculations

Posted on 2011-04-28
3
Medium Priority
?
291 Views
Last Modified: 2012-05-11
Hi,

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?

Thanks.

 Pivot-Table-Calc.xlsx
0
Comment
Question by:inforr
  • 2
3 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 1000 total points
ID: 35495583
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,

Dave
Pivot-Table-Calc-r1.xlsx
0
 

Author Comment

by:inforr
ID: 35502538
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?

Thanks,
Chris.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35502564
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

Dave
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

873 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