Pivot Table - Average column

I would like to create a pivot table that looks similar to the attached file.  Is this possilbe, if so, how?
2010-04-26-1133.png
jmkbrownAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Formatting a PivotTable to match your picture will be an exercise in futility. But I can reproduce the results (with different layout) as follows:
1) Make sure that your data is normalized. You need a table like shown in columns A:C, with header labels above each column. I chose Category, Date and Value as the three header labels
2) Create a named range that describes your data. This will make updating the PivotTable easy as new data comes in. Use the Insert...Name...Define menu item and create a range named SalesData using a Refers to formula like:
=$A$1:INDEX($C:$C,COUNTA($A:$A))
3) Right-click any cell in the range, then open the Data…PivotTable and PivotChart menu item
4) At the second step of the PivotTable wizard, change the Range to SalesData
5) Drag the Category and Date header labels into the "Drop Row Fields Here" items area of the PivotTable. Drag the Value header label to the "Drop Data Items Here" area of the PivotTable
6) Right-click any cell under Category in PivotTable, then choose Field Settings. Choose the option for None under Subtotals.
7) Right-click any cell under Date in PivotTable, then choose Group and Show Detail...Group. Control-click to select summarizing by both Month and Year.
8) Drag the Date header label one column to the right. This will array the PivotTable horizontally.
9) Right-click any cell under Years in PivotTable, then choose Field Settings. Choose the option for Automatic under Subtotals.
10) The formula for the monthly average is something like the following:
=SUM($G6:$R6)/MATCH(99^99,GETPIVOTDATA("Value",$E$4:$E$4,"Date",{1,2,3,4,5,6,7,8,9,10,11,12},"Years",LEFT(LOOKUP("zzzzz",$E$6:$E6),4)),1)
Where a cell address appears in the above formula, you must type it.
E4 is the top left cell in the PivotTable
G6:R6 contains the monthly data for Category A
Copy this formula down. The bit with LOOKUP returns the year for that row as a string.
11) The formula for the monthly average percent is something like the following:
=SUM($G6:$R6)/GETPIVOTDATA("Value",$E$4,"Years",LEFT(LOOKUP("zzzzz",$E$6:$E6),4))
12) I put the YTD cumulative rows at the top of the PivotTable because if they were at the bottom they might get overwritten when you refresh the PivotTable.
=GETPIVOTDATA("Value",$E$4,"Date",COLUMNS($G1:G1),"Years",2009)+F1         formula for 2009 YTD
=IF(GETPIVOTDATA("Value",$E$4,"Date",COLUMNS($G2:G2),"Years",2010)="","",SUM(GETPIVOTDATA("Value",$E$4,"Date",COLUMNS($G2:G2),"Years",2010),F2))         formula for 2010 YTD
=IF(G2="","",(G2-G1)/G1)       formula for % change YTD
PivotTableQ26078479.xls
0
 
jmkbrownAuthor Commented:
This is going to work perfectly!!!

Thank you!
0
 
byundtCommented:
jmkbrown,
Please continue to post in this thread if you need help in implementation. There were a number of tricks I used to get the formulas, and it may not be clear how they need to be modified to suit your worksheet layout.

For future reference, you might have gotten a quicker answer had you posted a sample workbook instead of a picture. I spent about 10 minutes just doing that. When the first task is creating a workbook with sample data to match the picture, a lot of Experts move onto the next question.

Thanks for the kind words and grade!

Brad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.