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

Question

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.

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.

Answer 1

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.

Answer 2

Try this:

• Example: = B4/DATEDIF(A9,A13,"M")

Explanation:

• Average spent per month = Total/Number of months
• Average spent per month = Total/DATEDIF(First date,Last date,"M")

Answer 3

Yes Shure, the response is:

If your editor its in Spanish:

=(SUMAR.SI(A9:A,">="&FECHA(AÑO(HOY()),MES(HOY()),1),B9:B)-SUMAR.SI(A9:A,">"&FIN.MES(HOY(),0),B9:B))/CONTAR.SI.CONJUNTO(A9:A,">=" &FECHA(AÑO(HOY()),MES(HOY()),1), A9:A, "<=" &FIN.MES(HOY(),0))

Or if your editor its in English:

=(SUMIF(A9:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),B9:B)-SUMIF(A9:A,">"&EOMONTH(TODAY(),0),B9:B))/COUNTIFS(A9:A,">=" &DATE(YEAR(TODAY()),MONTH(TODAY()),1), A9:A, "<=" &EOMONTH(TODAY(),0))

in the cell B5, corresponding to average spent per month

For practical purposes i recomend set one fixed cell for "start date" and other one for "end of period". Then

Spanish:

**REPLACE**                         **BY**
FECHA(AÑO(HOY()),MES(HOY()),1)        B6
FIN.MES(HOY(),0)                      B7

English:

**REPLACE**                            **BY**
DATE(YEAR(TODAY()),MONTH(TODAY()),1)     B6
EOMONTH(TODAY(),0)                       B7

This changes assuming what B6 its the cell whit start date and B7 contains end date, both in format Month/Day/Year, be careful, only replace exactly this parts and conserve the symbol &

Test Here: Groceries Espenses

Succes

Answer 4

=Sum(B9:B11)/D1 D1= Countif(A9:A11,">0")