Help! I need information to calculate the average time spent by person on a task. To do so, I need a sum of the time spent by that individual / count of items they were assigned. I did the calculation to the right of the table manually, but if I refresh the data, the formulas don't follow and require manual copying etc. Is there a way to add a formula which takes the sum/count directly in the pivot table calculation?? See partial table below (I took a marker to delete the person and specific task information for privacy and security reasons):
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Need Help adding formula to pivot table
- Thread starter rdkapp
- Start date
Hi. Not sure why you need the "Count of Time Spent on Mark Up Production." Can't you just click on "Time spent on Mark Up Production" (which must be in the Values field), then click Value Field Settings, change "Summarize value field by" to Average, and click OK?
Upvote
0
Solution
Awesome Ron! Thanks so much. So simple and such an easy fix. I actually kept the "Count of Time Spent on Mark Up Production" as we need the count numbers. Then, I added an additional column and changed the Value Field Settings to Average and voila!
This just shows that I am far from an expert on Pivot Tables.
it'll take longer to find the answer, but in the long run, worthwhile - investigate PowerPivot and the use of 'measures'. Its exactly what you need, but I can't give an easy answer
Thanks pjmorris. I'm just learning about Pivot Tables. If I find myself using them often, I'm sure I will investigate PowerPivot. Thanks for the suggestion.
Upvote
0
Delighted Ron has fixed the problem, its a solution that works well provided the average is for 'Markup for Production' by the individual concerned. If you wanted to compare their time with 'Markup for Production' of all components (not sure that makes real world sense, but...) then a standard Pivot Table can't do that (I don't think). Power Pivot lets you create measures in which that calculation would be entirely possible. Its definitely worth learning - and actually PowerPivot creates pivottables that are almost identical to the standard, but they're cleverer.
The trick is to ensure you've ticked the 'Add this to the Data Model' when creating your pivot table (there are other ways, but this is simplest).
The book I use is: 'Power Pivot and Power BI' by Rob Collie and Avichal Singh, which is readily available. Its an easy read and well worth the effort - you'll become an Excel God in your organisation.
All the best.
The trick is to ensure you've ticked the 'Add this to the Data Model' when creating your pivot table (there are other ways, but this is simplest).
The book I use is: 'Power Pivot and Power BI' by Rob Collie and Avichal Singh, which is readily available. Its an easy read and well worth the effort - you'll become an Excel God in your organisation
.All the best.
Upvote
0
Similar threads
- Question
