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.

1 Like

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

My solution is to use the formula
Essentially, a rolling average of only fully completed months, excluding the current month.