Hi all - apologies if this has been covered, I searched but could not find it.
I’m working on my budget for 2022, and have actuals data for 2019-2021. Is there an easy way to look at average spend per month per category over the last 3 years? Like yearly budget but covering more than one year?
Thanks.
It will take a little work, @asparagus_plant303, but see the second bullet at the end of this post. I think you can hack on the query in the hidden area of the Yearly Budget to get what you need.
You can play with this formula that I put on each row of the category sheet.
Just insert a column and paste formula.
This gives average spend over the past year/12. I think it works fine, might have some issues perhaps if there is $0 spend in a month. This won’t break it out by month, but give an average for the month in a given year.
I believe I got this from Heather.
=if(C2="Expense",IFNA(-(QUERY(Transactions!$B$6:$E,"SELECT sum(E)/12 WHERE D = '" & $A2 & "' AND Year(B)=" & YEAR(I$1) & " LABEL sum(E)/12 ''")),""),IFNA((QUERY(Transactions!$B$6:$E,"SELECT sum(E)/12 WHERE D = '" & $A2 & "' AND Year(B)=" & YEAR(I$1) & " LABEL sum(E)/12 ''")),""))
In this case, my I column is 2021, so you can simply reference that, or put in a manual year, or try this year -3.
Thank you, this worked great! I just put 3 columns with the year hardcoded to 2021, 2020 and 2019.