@Mark.S - I apologize - of course the pivot table was correct - It didn’t “miss” the $1000 charge - There were just several “payments” to BSF books that brought the total for June down! DUH! Thanks for all your help - I will sign off this topic for a while now!
Adding pivot table (@brettanicus ) and adding your (@Mark.S) ARRAY formula to a new column was the key for me.
Good job figuring out what was making up that number! One of the things I like about pivot tables is that you can double-click a number that you’re questioning, and it automatically creates another tab with all of the detailed transactions that make up that number. Then when you’re done looking at the detail, you can just delete that temporary tab.
I did this but it’s still giving me incorrect figures. Do I keep the “Sort By” field as “Month” and “Year” or change those for the column selections? Seems like it’s giving me very low figures for what they ought to be.
Sweet! I didn’t know about double click in pivot table! It just keeps getting better!
Welcome back @alkrongold ! I gave you bad advice about using AVERAGE in the pivot table itself; it will only give the average “Transaction” amount by category. This morning I will summarize this thread to show you what you need to do. Hold tight.
And if you’re feeling a little more adventurous, the AVERAGE formula could be enhanced to cover the year as the pivot table expands, so the formulas don’t need to be adjusted every month.
=AVERAGE(FILTER(ARRAYFORMULA(IF($C3:$N3="", 0, $C3:$N3)), ($C$2:$N$2 < EOMONTH(TODAY(), -1) + 1), NOT(ISBLANK($C$2:$N$2))))
Some of this just depends on what you are comfortable with maintaining 
I’ve still got problems with my pivot table NOT picking up all transactions. Take my mortgage payment:
The pivot table is missing a payment for Jan 2024, yet it is clearly in Transactions (1/3, although it does have a December date in the Week column)
double-clicking the TOTAL column in the Pivot Table (Thanks @brettanicus!) yields:
This one shows the Mortgage category is missing for Jan 2024 (Col B) in the Pivot Table:
And this screenshot is showing the Transaction in question from the Transactions sheet (cleared 1/3/24):
NOW what am I missing? 
What is the Month value for the 1/3/2024 Mortgage transaction on the Transactions sheet?
The month value is 1/1/24, but week is 12/31/23.
@Mark.S , unless I did something wrong, that formular doesn’t work for me:
1150+1752=2902 (as seen in total, far right) but divided by 8 should be 362.75 (like your previous formula gave), not 414.571
You would need to remove the Grand Total column from the pivot table, so the row only has per month totals.
Oh wait, the formula intentionally excludes the current month, so it’s 7 months, not 8.
Maybe you had the range selecting the partial August month.
The Grand Total column might be fine to leave, because it won’t be interpreted as a number/date.
Yes, 414.?? Is the average of 7 months.
My understanding of the pivot table is that whole months are included in the grand total so the col labeled 8/1 includes the whole month of August, but if you exclude the current month, all totals for this year will be /7, not /8.
Yes, the formula intentionally excludes the current month, because let’s say today is 8/3 … the total for August will be relatively small (maybe zero) and averaged with otherwise full months worth of data. Excluding the current month means the average is based only on full months worth of data.
But, the formula could certainly be changed if you prefer having the current month included.
What would be the formulas either way::
(current month EXcluded)
and
(current month INcluded)
Summary of Thread:
In this scenario, we want to calculate the average monthly cost by category in the current year. We want to prep the data easily without needing a lot of formulas. We’ll use Pivot Tables to prep the data and write an Average formula against the results.
There is a how-to guide that Tiller put together on pivot tables (thanks @Clint.C for providing that!) For this example, we’re using Google Sheets:
Visualize Your Financial Data With a Pivot Table
To make the Transactions sheet more useful for pivot tables, be sure it includes columns with values for Year and Group.
From the Transactions sheet, select the entire table or click on any cell in the table with data, then choose Insert > Pivot Table > New Sheet. Make the following selections in the pivot table editor (in this example, Year is filtered to 2024).
We end up with the sum of amounts by category for each month in 2024. Note that we only have through August so far, and August will be a partial month. Also, by default, each row and column checks the “Show totals” box, which creates totals that we don’t want for prepping data to calculate averages.
So: let’s uncheck the “show totals” boxes but leave the current month in the results to be dealt with in @Mark.S 's Monthly Avg formula. Insert a column to the left of A and name it “Monthly Avg.” Our results now look like this:
Paste the following formula beside your first result row, then drag the formula down well past the last row so you have room to grow; those blank rows will just show $0.
=AVERAGE(FILTER(ARRAYFORMULA(IF($C6:$N6="", 0, $C6:$N6)), ($C$5:$N$5 < EOMONTH(TODAY(), -1) + 1), NOT(ISBLANK($C$5:$N$5))))
Here’s the basic breakdown of Mark’s formula:
Note that we chose to still display the month-to-date value for August in the pivot for informational purposes only, so we don’t add a filter to exclude a month that we forget to remove later
Now our sheet looks like this:
@alkrongold would like to add additional Row values to organize and expand/collapse. For example, I added Group to the pivot table editor in the Rows section, optionally checked “show totals” for Category, and we get this:
Somebody good with math will have to tell me if this is at all accurate. 
You can make a copy of the sample file Pivot Tables - Sample Data.
Current month excluded:
=AVERAGE(FILTER(ARRAYFORMULA(IF($C3:$N3="", 0, $C3:$N3)), ($C$2:$N$2 < EOMONTH(TODAY(), -1) + 1), NOT(ISBLANK($C$2:$N$2))))
Current month included:
=AVERAGE(FILTER(ARRAYFORMULA(IF($C3:$N3="", 0, $C3:$N3)), $C$2:$N$2 <= TODAY(), NOT(ISBLANK($C$2:$N$2))))
When I copy the formulas I get strange outcomes. See screenshot first one gives me a wrong number and the second average is 0? :
We can’t see the numbers for the first one in your screenshot.
What is the formula in the second one?
Actually the rows are off … you’ll need to adjust the formula, because your sheet has a differing number of rows. One row should highlight the month dates, the other row should highlight the month sums.
