Help in creating a formular

[samc2626]

I would like to be able to calculate in the Totals page a daily/weekly average using Sep 16, Oct 16, Nov 16 and so on eventual completing a full year.

i would like it to only calculate the data in each month as it is entered.

example:total number of days in the weeks (using the month worksheet) = average spend per week.

month Sep 16, week 1 Day 5/09/2016 spend $40 average 40
Day 6/09/2016 spend $40 average 40
week 2 spend $20 average 30
Day 13/09/2016 spend $40 average 40
week 3 spend $96 average 52
week 4 spend $30 average 51.5
accumulating each week and total averages.

hope this make sense. i have attached a sample workbook.

 

[Hui]

Sam

Firstly, Welcome to the Chandoo.org Forums

If your worksheets are always the same format you can use

E3: =INDIRECT("'"&TEXT(C3,"mmm yy")&"'"&"!p29")
Copy that down

If they change in the number of rows
E3: =SUMIF(INDIRECT("'"&TEXT(C3,"mmm yy")&"'!A:A"),"Total",INDIRECT("'"&TEXT(C3,"mmm yy")&"'!P: P"))
Copy that down

 

[samc2626]

Hi Hue,

Thanks for the warm invite,

i do not have enough knowledge with formulas so please forgive me, i have copied the both into my spread sheet but nothing happens.

again excuse my lack of knowledge but would you be able to get it working in my spread sheet and sen it back to me, or provide more details on how to implement the formula.

many thanks
Samc2626

 

[samc2626]

Hi Hue,

Any updates on this.

 

[Hui]

see attached

 

[samc2626]

hi Hui,

thank you that is awesome,
can i ask you with column F, i am trying to get it to show the weekly average e.g. in October there are 4 weeks in the first week it would show the average for the total amount for 1 week for October, in the second week it needs to show the average for the 2 weeks, 3rd week for 3 weeks and so on for each month.

or is it better to average each month by the day?

what would the formula be to have excel work out the current date and average that month accordingly ether weekly or daily?

 

[samc2626]

hi Hui,

Any update on the above?
Also i have been trying to get the below to work with out any luck?
are you able to get this working for me please.
E3: =SUMIF(INDIRECT("'"&TEXT(C3,"mmm yy")&"'!A:A"),"Total",INDIRECT("'"&TEXT(C3,"mmm yy")&"'!P: P"))

appreciate your help.

 

[Hui]

Unless you specifically need the weekly averages why extract them
Wouldn't the daily average for a month be enough?

There is insufficient information in your file to understand what you are trying to achieve before we can answer a why question

 

[Hui]

You can work out the Daily Averages (Total / No days per month) or Workday Averages (Total / No workdays per month) per month quite simply

see attached file