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
=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