Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table multiple grand totals
How can I create multiple grand totals in a Pivot Table in Excel 2007? For
instance, one Sum and one Average. I want to be able to see the total for the column and also the average for the column. Is this possible on the same table or do I have to make two seperate tables? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table multiple grand totals
Hi,
Just add the calculated field to the VALUES area twice. Then change one of the calculation to average using the Field Setting option. I would give you a more specific answer if you showed us a sample of your data. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "RoofIL" wrote: How can I create multiple grand totals in a Pivot Table in Excel 2007? For instance, one Sum and one Average. I want to be able to see the total for the column and also the average for the column. Is this possible on the same table or do I have to make two seperate tables? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table multiple grand totals
I see what you mean, but can it be done another way so that I don't have
twice the columns? Data: Project Labor Material Disposal A Res. 10 50 10 B Res. 15 60 15 G. Total 25 110 25 I would like it to be: Project Labor Material Disposal A Res. 10 50 10 B Res. 15 60 15 G. Total 25 110 25 Average 12.5 55 12.5 "Shane Devenshire" wrote: Hi, Just add the calculated field to the VALUES area twice. Then change one of the calculation to average using the Field Setting option. I would give you a more specific answer if you showed us a sample of your data. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "RoofIL" wrote: How can I create multiple grand totals in a Pivot Table in Excel 2007? For instance, one Sum and one Average. I want to be able to see the total for the column and also the average for the column. Is this possible on the same table or do I have to make two seperate tables? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table multiple grand totals
Hi
Regrettably that's the way it works. If you Pulled Your Data button back from Total, so Average was appearing under Sum, you would get double the number of Rows. However, with that layout, you could put the following Event code on your PT sheet, and that would hide the Average Rows apart from the Total Average. Change the Const Colno in the Code to the Column Number where your Sum of Value and Sum of Average labels appears. Private Sub Worksheet_Activate() Dim c As Range Application.ScreenUpdating = False PivotTables(1).PivotCache.Refresh Cells.Select Rows.Hidden = False Const Colno = 7 '<=== Change to suit your Column For Each c In Intersect(UsedRange, Columns(Colno)) If UCase(Left(c, 7)) = "AVERAGE" Then c.Rows.EntireRow.Hidden = True End If Next Cells(1, Colno).Select Application.ScreenUpdating = True End Sub Copy the Code above Right click Sheet tab where PT exists View Code Paste code into white pane that appears Alt+F11 to return to Excel This is event code which will be triggered automatically. -- Regards Roger Govier "RoofIL" wrote in message ... I see what you mean, but can it be done another way so that I don't have twice the columns? Data: Project Labor Material Disposal A Res. 10 50 10 B Res. 15 60 15 G. Total 25 110 25 I would like it to be: Project Labor Material Disposal A Res. 10 50 10 B Res. 15 60 15 G. Total 25 110 25 Average 12.5 55 12.5 "Shane Devenshire" wrote: Hi, Just add the calculated field to the VALUES area twice. Then change one of the calculation to average using the Field Setting option. I would give you a more specific answer if you showed us a sample of your data. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "RoofIL" wrote: How can I create multiple grand totals in a Pivot Table in Excel 2007? For instance, one Sum and one Average. I want to be able to see the total for the column and also the average for the column. Is this possible on the same table or do I have to make two seperate tables? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table multiple grand totals
Excel 2007
A regular average would work if Project items did not repeat in the PT data source. A sum and average subtotal for a dummy group would work for that. Otherwise use helper columns or external tables: http://www.mediafire.com/file/zqzomt...03_16_09a.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grand totals Pivot table | Excel Discussion (Misc queries) | |||
Pivot table row grand totals | Excel Discussion (Misc queries) | |||
Need help with grand totals in pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Grand Totals - Hide Some, Not All | Excel Discussion (Misc queries) | |||
Pivot Table Grand Totals | Excel Worksheet Functions |