Calculate monthly average in pivot table?

Is there a way to calculate a category/group monthly average in a Google Sheets pivot table? Below is a shared sheet for reference. The pivot table is in the first tab. Feel free to edit if you’d like.

1 Like

You could use =average(range) in a separate column and do this for each group’s total row. But as the pivot changes/grows, these static averages are going to either break the pivot (as it grows horizontally) or become inaccurate (as it grows vertically).

So i’d recommend building your own worksheet not using a pivot table and use =averageifs to pull out monthly averages for each category.

https://support.google.com/docs/answer/3256534?hl=en

1 Like

@hbwilliams22 - Great question and thank you for sharing that example sheet!

My solution is to use the formula
=AVERAGEIFS(D11:O11,$D$7:$O$7,"<="&EOMONTH(TODAY(),-1)).
Essentially, a rolling average of only fully completed months, excluding the current month.

2 Likes