Katy has attended:
Excel Advanced course
PowerPoint Intermediate Advanced course
Average formulas and pivot charts
Hello
I have recently created a master spreadsheet which contains the following column headers:
Month
Number of weeks worked
Advisor
Advisor's office
I would like to be able to add a column that calculates the average number of weeks worked by month. Ideally, I would then like to create a pivot chart which shows the average number of weeks worked by month and add filters, which I could use to filter by average number of weeks worked per office and by advisor.
I was wondering if this was at all possible?
Many thanks
Katy
RE: Average formulas and pivot charts
Hi Katy
Thanks for getting in touch. You should be able to achieve all of that through a PivotChart without an extra column in your data.
A suggested layout could be:
* Month on the Column field
* Advisor on the Row field
* Number of weeks worked on the Value or Data field
In the top-left of your table it will say "Sum of Number of weeks worked" or possibly "Count of Number of weeks worked". Right-click this label and choose Value Field Settings. Under the heading Summarize field by choose Average.
This should then display what you're after.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
