# Average per category per month
**URL:** https://community.tiller.com/t/average-per-category-per-month/3214
**Category:** Google Sheets
**Tags:** how-to
**Created:** [March 15, 2020, 12:15am UTC](https://community.tiller.com/t/average-per-category-per-month/3214 "2020-03-15T00:15:42Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@joshmccormackpt](https://community.tiller.com/u/joshmccormackpt)
#### Post date: [March 15, 2020, 12:15am UTC](https://community.tiller.com/t/average-per-category-per-month/3214/1 "2020-03-15T00:15:42Z")
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?
---
### Author:  [@Blake](https://community.tiller.com/u/Blake)
#### Post date: [March 15, 2020, 2:59am UTC](https://community.tiller.com/t/average-per-category-per-month/3214/2 "2020-03-15T02:59:18Z")
@joshmccormackpt
Use Category Tracker. Chose actual month or chose year and then divide cell E6 by 12.
Blake
---
### Author:  [@joshmccormackpt](https://community.tiller.com/u/joshmccormackpt)
#### Post date: [March 15, 2020, 5:50pm UTC](https://community.tiller.com/t/average-per-category-per-month/3214/3 "2020-03-15T17:50:46Z")
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?
---
### Author:  [@susandennis](https://community.tiller.com/u/susandennis)
#### Post date: [March 15, 2020, 6:05pm UTC](https://community.tiller.com/t/average-per-category-per-month/3214/4 "2020-03-15T18:05:49Z")
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!
---
### Author:  [@Blake](https://community.tiller.com/u/Blake)
#### Post date: [March 16, 2020, 2:31am UTC](https://community.tiller.com/t/average-per-category-per-month/3214/5 "2020-03-16T02:31:19Z")
Change dates to what you want to see. Blake
---
### Author:  [@jono](https://community.tiller.com/u/jono)
#### Post date: [March 16, 2020, 9:28pm UTC](https://community.tiller.com/t/average-per-category-per-month/3214/6 "2020-03-16T21:28:05Z")
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
---
### Author:  [@joshmccormackpt](https://community.tiller.com/u/joshmccormackpt)
#### Post date: [March 17, 2020, 12:11am UTC](https://community.tiller.com/t/average-per-category-per-month/3214/7 "2020-03-17T00:11:27Z")
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.
---
### Author:  [@jono](https://community.tiller.com/u/jono)
#### Post date: [March 18, 2020, 12:44am UTC](https://community.tiller.com/t/average-per-category-per-month/3214/8 "2020-03-18T00:44:31Z")
Good solution too @joshmccormackpt.
Lots of ways to solve a problem using Google Sheets!