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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmkbrownAuthor Commented:
This is going to work perfectly!!!

Thank you!
0
byundtMechanical EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.