Is it possible to have both Grand Total and Grand Average in a Pivot Table?

[ANS]

If so, how?

Thanks!

[nitin.asalkar]

Can you attach your excel workbook and re-submit...so that I can work on it and send you back.

[ANS]

Pivot Table Example.xlsx

attached
thank you

[nitin.asalkar]

see if this helps

[ANS]

Hi Nitin,

This is nice, thank you.

1) Is there a way to do so without doubling the number of columns? That is, just add one additional column on the right with the grand average?
2) I should have been clearer as to what I wanted to achieve. I appologize. By "Grand Average" I meant having a monthly average, that is just the grand total devided by the number of months (in this case, it's divided by 12, but i have other pivot tables with less than 12)

Can anything be done now?

Thank you!

[Ace_XL]

Revert to your orignal table and in T4 use

=S4/COUNT(H4:R4)

Note your data contains only 11 months of data (No Sep-11!)

[ANS]

Hi,

Yes - of course that's the easy part.
What I wanted to have is an average within the pivot table (right now you can have either a grand total column OR a grand average column - I want both)
The reason for wanting it inside the PT is that with time I'll update the data it runs on to include more and more months - so the size of the PT will change, and I'll have to constantly update the average column manually. (and this is just an example... I have a file with about 50 pivot tables!)

Nice catch re Sep!

[JosephP]

you can't have just the grand total average - you have to have the data field twice for all columns.

[ANS]

Usually I can have either the Grand Total or the Grand Average (using the "summarize values by...."). I was hoping there's a way to display both

you can't have just the grand total average - you have to have the data field twice for all columns.

[JosephP]

not unless your source data is olap based. if you can use the powerpivot add-in (since you are using excel 2010) then you can make your data into a table, load it into powerpivot then create an olap pivot table and create a named set to display the data the way you want

[ANS]

I see. That's a bit diasspointing because it's very much within reach. I was hoping there's some way to get excel to do it.

Thanks all

not unless your source data is olap based. if you can use the powerpivot add-in (since you are using excel 2010) then you can make your data into a table, load it into powerpivot then create an olap pivot table and create a named set to display the data the way you want

[JosephP]

there is-either use the powerpivot add-in or add the data field twice as nitin.asalkar showed and hide the columns you don't want ;-)

[ANS]

there is-either use the powerpivot add-in or add the data field twice as nitin.asalkar showed and hide the columns you don't want ;-)

thanks .

[mgrabbani]

Please help me anybody by solving this questions...

1. You are creating a new formatting rule which will be used to format a PivotTable report. Which of the following formatting styles is NOT available while formatting all cells of PivotTable report based on their values in an MS Excel 2010 worksheet?
a. 2-Color Scale
b. 3-Color Scale
c. 4-Color Scale
d. Data Bar

2. Which of the following statements is true regarding PowerPivot in Microsoft Excel 2010?
a. One limitation of PowerPivot is that it does not allow access to Slicers when working on data in PowerPivot.
b. A connection to a server running SQL Server Analysis Services is required to answer PivotTable queries by the PowerPivot engine.
c. Version control and tracking is supported by PowerPivot.

3. Which of the following statements is NOT true regarding import of data into PowerPivot?
a. Data can be imported into PowerPivot by either selecting list of tables or by writing SQL query.
b. The data being imported into PowerPivot can be filtered before the import.
c. The PowerPivot Import Wizard provides you with an SQL Query builder.

4. Which of the following statements are true regarding PowerPivot in Microsoft Excel 2010?
a. PowerPivot streamlines the process of integrating data from multiple sources like databases, spreadsheets, reports and text files.
b. PowerPivot does not allow access to slicers when working on data in PowerPivot.
c. Version control and tracking are NOT supported by PowerPivot.

5. Deleting a PivotChart report automatically deletes the associated PivotTable report.
a. True
b. False

[JosephP]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[mgrabbani]

Thanks for your kind information.