Excel Pivot Table - Averaging by Category

2
votes

I'm trying to create an average by category in a pivot table. This is the first time I've created a pivot table so sorry if the answer is staring me in the face. My raw data looks like:

Date,   Transaction type,   Description,     Paid out,   Paid in,    Balance,   Category
Mar-13, Visa,   SHOP,    £4.44, ,        £X,    Gifts
Mar-13, Visa,   SHOP,    £5.00, ,        £Y,    Children
Mar-13, Visa,   SHOP,    £6.00, ,        £Z,    Gifts
Mar-13, Visa,   CLOTHES SHOP YORK,   £8.00, ,        £A,    Clothing
Mar-13, Visa,   FOOD SHOP,   £11.96, ,       £B,    Food

My pivot table shows the information rolled up by Month and grouped by a category:

Row Labels  Sum of Paid out Sum of Paid in  Sum of Difference
2013            
   Jan        £Jan       £Jan                  £C
     Food       1              2                 -
     Car        2              3                     -
     Cash       6              6
   Feb         £Feb       £Feb                 £D
     Food       1              2
     Car        8              0
     Cash       2              3

The categories/data is made up in this case, but the desired outcome I'm after is to get an annual average, informing me how much comes in/out on average across the year per category... looking something like:

Row Labels  Sum of Paid out Sum of Paid in  Sum of Difference
2013
   Avg        £AvgIn     £AvgOut              £AvgDiff    
     Food       1              2                 -
     Car        5              1.5               -
     Cash       4              4.5        
   Jan        £Jan       £Jan                  £C
     Food       1              2                 -
     Car        2              3                 -
     Cash       6              6
   Feb         £Feb       £Feb                 £D
     Food       1              2
     Car        8              0
     Cash       2              3

Is this possible to achieve using a pivot table, as I can't seem to find a way to this at the moment using Excel 2010.

2
excelpivot-table

2 Answers

5
votes

Since it appears that your raw data is already grouped by month, you're able to do this pretty easily. You need to re-arrange your data, however -- the months and the categories need to be on different axes. For example, Category as ColumnLabels, with Values then Month as Row Labels. Then right-click one of the normal values for Paid Out, choose "Summarize Value As...", you'll see SUM is currently checked, just change to AVERAGE. Repeat for one of the Paid In values. The labels should change to help let you know if it worked.

enter image description here

Note that this will NOT work effectively if your source data comes in daily, for example. With only one entry per month, the SUM and the AVERAGE of the single entry are identical. This would not be the case if your raw data was daily (and you still grouped by month) -- you'd be switching from a monthly total to a daily average.

0
votes

enter image description here I am currently using LibreOffice, which is just the free version of Excel on a mac. It is basically the same thing, but anyways, do you see the top bar where it says =AVERAGE(B8,C8,D8)? Well, that is how you can incorporate the functions within the table. This is actually a pivot table I had to do for my Java class. All you have to do is click on the cell you want to edit, type =, the function name, (in your case, AVERAGE should be fine) and then any other kinds of functions you use. Hope this helps you out.