I want to average the “actual” cells in the YEARLY BUDGET - and exclude zeroes. I know about average and averageif, but I need to skip some cells so I only get the ACTUAL cells - ive tried various things with averageif and averageifs to no avail - please help?
I would like to get the average monthly amount actually spent for each category- does this already exist?
Pivot tables are great for doing ad-hoc analysis with minimum coding necessary. In your case, you would Average the amount instead of Sum. Assuming your Transactions sheet has Month and Year as mine does, add Year in the Column section. Your pivot table editor would look something like this:
1 Like
Thank you! I’m a noob who’s never used pivot tables, so something new to learn and play with! (Good thing!)
1 Like
@brettanicus Hi Brett,
There is a how-to guide that Tiller put together on Pivot tables. This might be a good starting point.
Visualize Your Financial Data With a Pivot Table
Clint
I’m trying this just like you outlined but it’s only showing me the average on a per transaction basis not a monthly average. How do I make it show a monthly average?
What do you have in the “rows” section of the dialog box? That will calculate your averages based on that level of detail, such as “Category” in my example. If it only has “Category,” like in my example, it will average by “Catgegory.” You might have added “Description.” Also, confirm that Amount in the Values section is set to “Average,” NOT “Sum.”
Are you familiar with expanding and collapsing pivot tables by various levels of detail? That is also something you’ll want to review in the help document link that Clint provided in the thread above. Then you can have both Category and Description in the rows and see the average for each.
1 Like
Here’s my settings. I’d like to drill down the monthly average from category type, to group, then to category. Let me know if I’m doing something wrong here.
Add Months to the Columns section, after Year.
Just curious; were you able to get your pivot table to work as you wanted @alkrongold ?
Actually, I got Covid and haven’t had time to try it or felt like it. Saved the emails though. Thanks! Sound like it should do exactly what I want.
1 Like
Sorry Brettanicus, you were asking Alkrongold!
Sorry to hear that. Hoping you have a quick recovery!
Yeah, I’m 76, so it’s kicking my butt a little! But I’m home and sort of doing OK.
1 Like
@brettanicus, I’m feeling a little better now from COVID (!), and decided to tackle pivot tables. I think these can do what I want, but I am having the following challenges:
- I don’t find average under Values in the pivot table editor, (if I use Amount, it does give me the total for each category by month), and
- Maybe I should have explained better, but what I want is an average monthly spending for the whole year (average of all the category sums for the year to date). I want to ignore the months in the future, as they are zero now). Hope this helps. - John
I think I got it! (Average was under Summarize By). Perfect!
THANKS EVERYBODY, especially @brettanicus, @Clint.C, @alkrongold, and @heather!
1 Like
Double-check that the Amount Summarized by AVERAGE is not the average transaction amount for the month, as opposed to the “average monthly amount” (average of monthly sums) 
Thank you - i will check this. If it’s the average for the month instead of the average sums, how to fix this?
Again, what i want is the average of all the category sums for the year to date.
You could change the pivot table Amount to Summarize by SUM to get the monthly category sums and then use AVERAGEIF() in a column next to the pivot table, now that you’ve got the monthly sums in a nice, compact table.