I am trying to calculate my monthly average expense for a category as time passes and currently using the formula:
=C15/MONTH(TODAY())
C15 is my total running category expense for the year. This formula kind of works but is not very accurate as each month starts off with $0 expenses and then increases as the month progresses.
Is there one that will more accurately give me my average monthly expense? Possibly only including fully completed months or should I include the partial month to get my monthly average?
One way to approach this might be to be more specific with the month number, (e.g., 8.9 for September 24 instead of 9). Try this:
=C15/((today()-Date(Year(today()),1,0)/30)
It won’t be 100% accurate since not every month has 30 days but it’s a closer approximation than your current formula. Let me know how it works out!
ETA: If you want to only calculate the average for complete months, you can set C15 to query the sum of your expenses for months prior to the current then divide by month(today())-1.
Just thinking here. Would it make sense to move to weekly budgeting? I am no expert as I never really budgeted. Not sure if weekly is overkill. 52 versus 12 is surely something to think about. Seems to me if someone is super serious about budgeting and getting control over things, that weekly might be better than monthly but it will definitely involve more of a time commitment. It seems Tiller is set up for monthly and yearly. At least with weekly, you will know real fast how you did whereas a month is a long time, especially if you are trying to gain control over the situation sooner rather than later. Maybe you start out with weekly and then switch to monthly as you gain more control over your finances.
I am in a similar situation Blake as I do not have a rigid budget. I more so like to see my monthly trends. I know how much total spend I should average monthly and that’s the number I focus on.
@hbwilliams22, check out the “Spending Trends” tab. It shows your daily spend and averages it over the selected period of time (week, month, quarter, year). This appears to be exactly what you are looking for.
Thanks so much for this! I was trying to figure out this very thing and this post helped me a ton! I am good with your thought of just using months that are complete, so I used your formula but made one tiny change. Here is how I did it (and it is working great for me):
=C15/(MONTH(TODAY())-1)
Just wanted to put this in here in case it would help anyone
Thanks all, for of the information. This also helped me solve the challenge of understanding my monthly averages. I decided to insert a column to the Yearly Budget and used the formula below to get the averages. I decided to use a fraction-of-the-month calculation so its relatively accurate throughout the month.
I do realize that this will only work when looking at the current years budget. I might try and tackle that sometime later as I will want to look back at historical averages.
