• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Pivot Table Calculations

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
inforr
Asked:
inforr
  • 2
1 Solution
 
dlmilleCommented:
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
 
inforrAuthor Commented:
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
 
dlmilleCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now