Showing posts with label asker: AndreasDK; Google Spreadsheet. Show all posts
Showing posts with label asker: AndreasDK; Google Spreadsheet. Show all posts

Monday, April 25, 2011

yogi_Calculate Average Amount Per Month ToDate Starting A Specified Month

Yogi Anand, D.Eng, P.E.                                   Google Spreadsheet                      www.energyefficientbuild.com
AndreasDK said:
I would like a line sayng something lake "Monthly average buy sum", which looks at the dates for the purchases and divides TOTAL out with number of months from the start of the spreadsheet (April 2011) to the current month.

So that if I buy something next month for the price of kr. 1 (see example in document) the Monthly average sum would say 517,28 / 2 (April and May 2011).
Should I not buy anything in June no entry would be made with a date in June bout Average monthly sum should still include the month, so that it says 517,28 / 3 (April, May and June) as these months have passed since the start of the spreadsheet.

------------------------------------------------------------------------------------
In the following solution I have generalized it by specifying the StartingMonth in cell B4

yogi_Calculate Monthly Spending

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

AndreasDK said:
I have just made this spreadsheet.
UDGIFTER TIL BARBERING
Dato for køb: Varens navn: Butik: Pris
14-04-2011 Merkur 42C Proshave.dk kr 229
14-04-2011 Derby Extra skraberblade 10 stk Proshave.dk kr 25
14-04-2011 Proraso 50ml barberskum Eucalyptus / menthol Proshave.dk Gratis
14-04-2011 Forsendelse Proshave.dk kr 39,00
19-04-2011 Floid Aftershave splash 150ml Vintagescent.com kr 81,66
19-04-2011 Semogue 1305 barberkost Vintagescent.com kr 104,33
19-04-2011 Forsendelse Vintagescent.com kr 37,29
TOTAL: 516,28

The field showing the Total amount uses =SUM(D4:D10), whisch is fine but I would like to show average monthly spending beneath that line.
Is there a function that can take the sum of =SUM(D4:D10) and divide it with the numer of months since creation of the sheet?

--------------------------------------------------
Please note that this spreadsheet's Locale is set to Denmark ... so ,(comma) is used for decimal instead of . (full stop) and ; (semicolon) is used as argument separator in functions