Solved

Running Average in Pivot Table/Chart

Posted on 2011-02-22
6
3,106 Views
Last Modified: 2012-05-11
Can anybody tell me if there is a way to construct/display a running average in a pivot table/chart.
I am trying to show the time it takes to close a ticket and would like to display the running average to show if the average closure time is going up or down.
Any help is appreciated.
0
Comment
Question by:greentatertot1970
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 34954490
You need to create as Pivot Field (Calculated Field) to do that to avoid creating averages of averages.  I believe you want weighted average?

Here's a couple online tips.  If you load a non-confidential spreadsheet, I'll show you how to build it in, if further assistance is required:

http://www.mrexcel.com/forum/showthread.php?t=30526 

http://excel.tips.net/Pages/T002900_Weighted_Averages_in_a_PivotTable.html

Again, if you're going down a different path - upload a sample we can work it no problem.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34954535
I may have misread.  Let me work up an example which may be more in line to your query.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34954586
Yes - perhaps the solution is simpler than I initially thought.  I created a sample dataset with random start/end dates, then fixed those.  I then created a running average column which I pivoted and trended.

Is this going the direct you're looking for?

Dave sampleRunning-Average-Pivot-r1.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34954718
It doesn't appear Pivot Tables support MOVING AVERAGES, again, this can be something generated to the SIDE of the  Pivot Table.

http://chandoo.org/wp/2009/04/28/calculate-moving-average/  Provides an example if you read down the posts.


Dave
0
 

Author Comment

by:greentatertot1970
ID: 34954732
Yes... this looks like the direction I am wanting to take. I'll look at what you have provided. Thank you!
0
 

Author Closing Comment

by:greentatertot1970
ID: 35017574
Thanks Dave!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

751 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