Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

average formulas and pivot

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Average formulas and pivot charts

Average formulas and pivot charts

ResolvedVersion 2007

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

Excel tip:

Shortcut for deleting all comments in a spreadsheet

If you have entered multiple comments into a spreadsheet and wish to delete them all at once, you can achieve this by:

1. Holding down Ctrl, then Shift, then O - this will select all cells containing comments in the worksheet you are looking at.

2. Right-clicking on one of the selected cells, and selecting Delete Comment from the menu that appears.

3. Clicking anywhere else in the spreadsheet to deselect comments - all comments should have disappeared from the spreadsheet.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.