How to get Average of Grand Total in PivotTable

[cbs78]

I have a pivot table that calculates the sum of total expenses per month . Source is raw data in another worksheet.
I want to know the average per month. How do i calculate that in the pivot table ?
I know i can use the Average formulate to calcualte this outside of the pivot table.
I want to see if there is a way to calculate in the pivot table itself.

[TABLE="width: 316"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sum of Amount[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Restaurants/Dining[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD="align: right"]-347.33[/TD]
[TD="align: right"]-347.33[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD="align: right"]-500.32[/TD]
[TD="align: right"]-500.32[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD="align: right"]-787.7[/TD]
[TD="align: right"]-787.7[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD="align: right"]-492.17[/TD]
[TD="align: right"]-492.17[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD="align: right"]-1062.69[/TD]
[TD="align: right"]-1062.69[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD="align: right"]-710.09[/TD]
[TD="align: right"]-710.09[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD="align: right"]-541.08[/TD]
[TD="align: right"]-541.08[/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD="align: right"]-538.42[/TD]
[TD="align: right"]-538.42[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD="align: right"]-379.89[/TD]
[TD="align: right"]-379.89[/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD="align: right"]-653.64[/TD]
[TD="align: right"]-653.64[/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD="align: right"]-479.76[/TD]
[TD="align: right"]-479.76[/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD="align: right"]-474.12[/TD]
[TD="align: right"]-474.12[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]-6967.21[/TD]
[TD="align: right"]-6967.21[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Something like Average(6967/12)[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mohish]

Click on the desired field in the VALUES Section of Pivot, click "View Fields Settings" and select Average from the list.

 

[cbs78]

Thanks for the response but what you suggest is not the data i am looking for. I am looking to get the average of the Grand Total, not the individual totals for each month. So in my example, my grand total for 12 months is 6967. I am looking for a way to get average of 6967 for 12 months.

 

[Mohish]

This might not be possible in PivotTable but you can use column next to "Restaurents/Dining" to get the same values for the month but average value for the Grand Total and then hide the Grand total column of Pivot table. Use dynamic range in pivot source, the Grand average will be changed each time you refresh the pivot.

[TABLE="width: 319"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD] Restaurents/Dining (Pivot Column)[/TD]
[TD] Restaurents/Dining (Formulated Column)[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD="align: right"]-325[/TD]
[TD="align: right"]-325[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD="align: right"]-150[/TD]
[TD="align: right"]-150[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD="align: right"]-70[/TD]
[TD="align: right"]-70[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD="align: right"]-300[/TD]
[TD="align: right"]-300[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]-875[/TD]
[TD="align: right"]-175[/TD]
[/TR]
</tbody>[/TABLE]

Using below formula

=IF(A2="","",IF(A2="Grand Total",AVERAGE($B$1:B1),B2))

A2 holds start month.

 

[cbs78]

Not quite sure i understand. By Formulated Column do you mean calculated field ?