Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Calculations
Hi I currently have the following pivot table which shows rolling 12 month
range of data: Area 1/1/07 2/1/07 Grand Total A 100 200 300 B 500 600 1100 Total 600 800 1400 And I want to be able to show a % of Total for the Grand Total Column and % Total for only the current month reporting column (2/1/07). and update every month going forward. Next month % total for March instead of February. Can this be done? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Calculations
Since PT cannot use the Grand Total to calculate an item,
add two fields that calculate the totals and add yet another field to calculate the percentage. The result might look like this: Sum of Value Date Area 1/1/07 2/1/07 SumDat A 100 200 300 B 500 600 1100 SumAB 600 800 1400 PctAB 43% 57% 100% In your source data, add dummy data for all the months of the year. SumAB has this formula: =A+B SumDat has this formula: ='1/1/07'+'2/1/07'+'3/1/07'+'4/1/07'+.....+'12/1/07' If the initial source data does not show all the dates, it will not allow you to enter this formula. PctAB has this formula: =SumAB/(SumAB SumDat) formatted as Percentage. In Table Options, uncheck Grand Totals. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Calculations | Excel Worksheet Functions | |||
Calculations in Pivot Table | Excel Worksheet Functions | |||
Pivot Table Calculations | Excel Discussion (Misc queries) | |||
Calculations in pivot table | Excel Worksheet Functions | |||
pivot table without any calculations | Excel Discussion (Misc queries) |