Average per category per month

Google Sheets
1

Is there a way to add a calculation for average spent each month per category to an existing sheet or an easy way to create a new sheet with just that info?

2

@joshmccormackpt

Use Category Tracker. Chose actual month or chose year and then divide cell E6 by 12.

Blake

1 Like
3

thanks. What if I wanted to see the average for completed months only? The category tracker shows current month in progress. Is there a way to change it to completed months only or to set up a new sheet with completed months only?

4

iā€™m not sure iā€™m right so i removed this - cool idea tho and iā€™m learning a lot by working on an implementation so thanks!

5

Change dates to what you want to see. Blake

6

Hi @joshmccormackpt,
As @Blake wrote, you could adjust the start and end dates to include just the completed months you want to review.

You could then use the formula:
=DATEDIF(B4,B5,"M")
to calculate the Months between the start and end dates. Knowing the months, then you could automatically divide the category totals by the months to get the Average per month.

Using start and end dates from 1/1/2019 to 12/31/2019 shows 11 months, while 1/1/2019 to 1/1/2020 shows 12 months. So you might want to select the first day or the month for the end month or add 1 if using the last day of the month.

You could put the above formula in a column to the right of Column J. For the examples below, assume you put that formula in J1.

Then, adjust the formula in A7 from:
=iferror(QUERY($G$7:$I$200,"SELECT G, H WHERE I<>'Hide' AND G<>'' ORDER BY G ASC LABEL G '', H ''", -1),"")

to
=iferror(QUERY($G$7:$I$200,"SELECT G, H/"&J1&" WHERE I<>'Hide' AND G<>'' ORDER BY G ASC LABEL G '', H/"&J1&" ''", -1),"")

Note the H/ā€œ&J1&ā€ parts.

Let us know if that works for you.

Jon

7

I figured out another option in case it helps anyone else.

  1. Create a new sheet
    2). Column A is a list of all categories in alphabetical order
  2. Column B references the column tracker sheet amount for each category
    • need to filter it to the end of the previous month (e.g. since it is mid march I have dates
      on category tracker set for 1/1/20 to 2/29/20
  3. Column C has the following formula =ABS(B2/(month(today())-1))
    this creates the absolute value (I wanted positive values) of B2 (amount spent in category as of end of prior month from the category tracker/how many months that are completed in the current year).
  4. Column D is the amount budgeted per month
  5. 3rd column amount has conditional formatting turning it red if it exceeds the amount budgeted per month.
3 Likes
8

Good solution too @joshmccormackpt.
Lots of ways to solve a problem using Google Sheets!