What is the best running monthly average equation?

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.

1 Like

If you have not seen it, please check out the tab called “Weekly Analysis By Category”. It is weekly but it might be helpful.

One other solution might use Google Sheet’s YEARFRAC() function.

It calculates the fraction of a year between 2 dates.

=YEARFRAC(DATE(2019,1,1),DATE(2019,9,25),1)
That result is 0.73150

More info here:

You could also calculate dynamically the percentage of the month:
=DAY(EOMONTH(TODAY(),0)) displays the last day of the month

=DAY(TODAY()) displays the day today.
If you divide the 2nd one by the 1st one, you get the current percentage of the month.

1 Like

Thanks @Blake! Just a note you can find the “Weekly Analysis by Category” report in the free Tiller Labs Add-on for Google Sheets.

This would be super helpful if there was a monthly option. As I do not have a “weekly budget” I am always looking to understand my monthly habits.

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.

1 Like

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.

1 Like

@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.