9

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.

3
  • 1
    You should remove editing privileges - anyone who wants to experiment with your sheet can make a copy of it instead. Feb 23, 2017 at 18:31
  • Averageif/sumif ?
    – BCLC
    Feb 23, 2017 at 18:49
  • "Average spent per month" - is that really any different from =SUM(amount) / 12? - Oh, OK, I now see that the dates span multiple years. Feb 27, 2017 at 9:24

4 Answers 4

6

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.

0

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")
-1

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

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.