Need help with Averageif using a date range.

[coryh83]

I have a pivot table that I am using to capture daily usage of items. In column A(outside the pivot table) I have a daily average formula that pulls the average of the range. I currently have to adjust the range each day when the pivot table is updated. How can I have the range in the average column auto adjust as new dates are added. Thanks for the help

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]January[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Average[/TD]
[TD]Item[/TD]
[TD]1/1/18[/TD]
[TD]1/2/18[/TD]
[TD]1/3/18[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]widget[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

You can do that within the pivot: right click 'Grand Total', Summarize Values By> Average.

 

[coryh83]

I need the Grand Total column as well as the average. That is why I have opted to run the average outside the pivot table. Is there a way to have both inside the pivot table?

 

[steve the fish]

There sort of is but you have to mess around a bit. You could either do that or i could give you a formula if you want but that still has caveats. I think id do it the pivot table way. Google multiple grand totals for pivots and contextures site does a good example.

 

[steve the fish]

Actually scrap that. Just put the price or whatever the field is you want to sum and average in the values field of the pivot. Change one to sum and the other to average. Seems to work as intended.

 

[steve the fish]

Row Labels	Average of Price	Sum of Price

<colgroup><col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> </colgroup><tbody>
[TD="width: 84"]Month[/TD]
[TD="width: 97"]January[/TD]
[TD="width: 75"][/TD]

[TD="class: xl63"]widget[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="class: xl63"]Grand Total[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

</tbody>