Formulas For Average Subcategory Amounts in Date Range

[BillySpivy]

Hello,

R6 and R7 on the Monthly Budget sheet have the date range for the Subcategory average amounts from the Notes sheet. Are there formulas that can fill these amounts in Q10:Q72?

Thank you very much for your help

[Richard Buttrey]

Hi,

Q10 copied down

Please Login or Register  to view this content.

[BillySpivy]

Thank you Mr. Buttrey that works perfectly!

[BillySpivy]

I did some further checking and there may be something wrong. The amounts don't match what the actuals are. Please check my revised copy showing an extra column that takes averages ignoring zero amounts for each month if that subcategory didn't have anything.

[nick.williams]

Richard's formula gave you the average of each entry in the Notes sheet. Your "check" is against the average monthly totals which are on a previously hidden sheet. Pretty hard to know what you wanted

Something like this should work for the monthly non-zero averages:

=SUMIFS(Table4[Amount $],Table4[Subcategory],L10,Table4[Date],">="&$R$6,Table4[Date],"<="&$R$7)/SUM(IF(FREQUENCY(IF((Table4[Subcategory]=L10)*(Table4[Date]>=$R$6)*(Table4[Date]<=$R$7),MONTH(Table4[Date])),IF((Table4[Subcategory]=L10)*(Table4[Date]>=$R$6)*(Table4[Date]<=$R$7),MONTH(Table4[Date]))),1,0))

[BillySpivy]

My "check" is directly from the Notes sheet for the given year in cell $C$3. This seems like a less complicated way to get averages of the months for when values are added. It ignores months that do not have any entries.

Thanks

[nick.williams]

I suppose it is, looking at it in more detail, although it uses a helper sheet. Anyway, glad you have it sorted and thanks for taking the time to mark the thread solved and give out reputation.