I definitely now have the average for each month now, not the average of the monthly sums.
The challenge for me is that I only wnat the average to date, (not divided by 12).
How about selecting just the months you want to AVERAGE, something like this:
Cell A4
=AVERAGE($C4:$I4)
Cell A5
=AVERAGE($C5:$I5)
etc. (drag the formula down to auto increment the row)
Where I did not select the current month, since it’s a partial month and would skew otherwise full month totals.
Column A is not apart of the pivot table, just a column inserted along side the pivot table.
I added a Filter to the pivot table to only show this year for this sample data:
That might work for me - Thanks!
Good catch, Mark. 
I didn’t know that average in a pivot table would average the transaction amounts rather than by the sum of Category (or whatever fields you select for Rows.) Nice solution.
1 Like
This looks like it should meet my needs perfectly. But what is wrong below? -$1,150 is clearly not the average of C3:D3. (Just testing and experimenting.)
Also @Mark.S, when I Add Filter, I don’t see option for YEAR?
Finally (?), At the top of each column, I see dates that are the first day of each month, ie 1/1, 2/1 etc., yet the values reflect that whole month, apparently?
I see, looks like we need to treat blank cells as zeroes for AVERAGE.
How about this instead?
=AVERAGE(ARRAYFORMULA(IF($C3:$D3="", 0, $C3:$D3)))
Year is not included by default on the Transactions sheet - see add year data.
That’s just how Tiller chose to represent the Month data for transactions. So, 1/1 is the same as January, the month.
@Mark.S , That formula below works perfectly for that one range - How to adapt so
that I can select the cells to average?? Sorry for all the questions -
Fascinating, and very helpful answers!
=AVERAGE(ARRAYFORMULA(IF($C3:$D3=“”, 0, $C3:$D3)))
The pivot table is always going to be through the current month, so if I use the formula as you listed, but increase the cell range to current month, then I can drag it down, increasing the cell #, and all is good - Just need to make a new pivot table each month.
Or maybe just one at the end of the year! Thanks all! (No one response completely answers my original question, so none marked as “Solution” - whole thread needs to be digested for complete answer.) @Mark.S added info that was critical to ignoring “0” months in average.
Glad to hear you’ve got something workable, John! 
@Mark.S, Unfortunately, something is still not right - The BSF Books line has a $64.50 in July that is not in Transactions, and there is a $-1000+ charge in Transactions in June that is not showing at all in the pivot table.
I shouldn’t be showing Averages in the pivot table as I know I specified SUMs.
Yes, I’m sure I specified ALL rows and columns before inserting the pivot table.
And notice the monthly average in col A is 0 for Auto-service and BSF Books??
Anything else you notice? Your formula for the AVERAGE ARRAY is over my head - Do you feel it is still correct?
Are you using the pivot table Filters option? If so, please share a screen capture of that.
On the Transactions sheet, filter the Month column with 7/1/24 to double-check … it may be something you’re not expecting.
Does this transaction have a value/date in the Month column of the Transactions sheet?
What is the formula in cell A5 and A6?
No, no filters.
@Mark.S, now I see you’re asking for formula in A5 and A6. AHA - I thought I had expanded your through August, but it only has Jan and Feb (cols C and D)
=AVERAGE(ARRAYFORMULA(IF($C6:$D6=“”, 0, $C6:$D6))) I was sure I had expanded to Col J, but will try again! Thanks for your patience!
I expanded your ARRAY formula to col J, and that was preserved as I drug down for all categories and SOLVED the 0 entries.
However, the 1000+ charge in June is still missing from BSF Books. Other ideas about that?
Does this transaction have a value/date in the Month column of the Transactions sheet?
I’d probably do a Transactions sheet data filter with the Category and Month columns and select only BSF Books and 6/1/24, to see all the transactions together.
You could also try adding a Description row to your pivot table to have the option to see the transactions included in the sum. That adds a column, so you’d need to adjust your Monthly Average formulas.
Another option is to compare the pivot table value to the Yearly Budget actual value as a sanity check.
How do you filter the Transaction sheet?
Nevermind @Mark.S , I got it!