Google-sheets – Using Google Sheets, how can I calculate the average per month based on multiple rows

google sheetsgoogle-sheets-dates

I have a spreadsheet where I enter Groceries on each row every time I visit a store. I have calculated the overall sum, but now I would like to calculate the average cost per month for the groceries.

enter image description here

I don't even know where to begin on this. I have posted my spreadsheet (https://docs.google.com/spreadsheets/d/1R0zYMAigDULzm8oM2icsPK55BP162kVvctpx2cOSOIE/edit?usp=sharing) and gave editing priveleges to anyone who can attempt it. If someone already posted a solution and you think yours is better, please create a new row in the Summary section so I can compare them.

Best Answer

I'm guessing you basically want to sum up the expenses for each month, and then find an average amount per month.

Let's first sum up expenses for each month. Each row in your spreadsheet has a date, but that does not immediately link it to other rows within the same month.

So let's introduce a new column Month, derived from the date. There is a formula =MONTH, so let's use that. It takes a date as parameter, and returns 1 for January, 2 for February and so on:

 A: Date     B: Amnt   C: Month
 11/16/2016  $133      =MONTH(A2): 11
 1/7/2017    $ 30      =MONTH(A3): 1
 1/15/2017   $ 50      =MONTH(A4): 1
 2/21/2017   $120      =MONTH(A5): 2
 2/28/2017   $300      =MONTH(A6): 2

Then we can write a =QUERY to group expenses per month:

=QUERY(A2:C;"select C, sum(B) group by C"; 0)

This should give us:

11  $133
 1  $80
 2  $420

So far, so good. But wait a minute: What if we add an expense ($44) for November 2017? That reveals a problem - November 2016 and November 2017 will be summed up together, so:

11  $177 ($133+$44)
 1  $80
 2  $420

So we don't want to group just by month - we want to group it by year/month, so that November 2016 is summed separately from November 2017.

We can fix that by appending =YEAR to the Month column:

 A: Date     B: Amnt   C: Year/Month
 11/16/2016  $133      =YEAR(A2) & "/" & MONTH(A2): 2016/11
 1/7/2017    $ 30      =YEAR(A3) & "/" & MONTH(A3): 2017/1
 1/15/2017   $ 50      =YEAR(A4) & "/" & MONTH(A4): 2017/1
 2/21/2017   $120      =YEAR(A5) & "/" & MONTH(A5): 2017/2
 2/28/2017   $300      =YEAR(A6) & "/" & MONTH(A6): 2017/2
 11/1/2017   $ 44      =YEAR(A7) & "/" & MONTH(A7): 2017/11

That will automatically fix our query result, so it is now:

2016/11  $133
2017/1   $ 80
2017/2   $420
2017/11  $ 44

If our query result is output in column F, we can now get the =AVERAGE per year-month:

=AVERAGE(F2:F)

I have made an example spreadsheet to demonstrate this, feel free to copy it.

Related Topic