Weekly Average formular

[samc2626]

Hi,
i have a weekly average spend that i need to keep track of.
View attachment 36905
in the above table for December i would like to have it only show the average for the current week within that month instead of always dividing it by the total number of weeks with in the month e.g.
week 1 for December to be divided by 1 week
week 2 for December to be divided by 2 week
week 3 for December to be divided by 3 week
week 4 for December to be divided by 4 week

Also i have tried implementing the below formula but its not working, are you able to advise.
E3: =SUMIF(INDIRECT("'"&TEXT(C3,"mmm yy")&"'!A:A"),"Total",INDIRECT("'"&TEXT(C3,"mmm yy")&"'!P: P"))

as my monthly spreadsheets are not always formatted the same.

thanks.

 

[vletm]

Your 'so called attachment 36905' gives this ...

If You really would like to get something ...
even ideas then
something should do.

 

[samc2626]

Sorry here is the sample.

 

[vletm]

... because Your monthly sheets could be different then
Why You won't use 'just one sheet' like ~all?
( I quickly copied some columns - not correct; You gotta do it)
and
take weekly values out like ~weekly?

 

[samc2626]

unfortunately i need to also keep each month separate.
so your suggestion does not help.

 

[samc2626]

tried copying it into my spreadsheet but its not working

 

[vletm]

Each month separate
... You could FILTER each month separate
its not working
... that's true if just copy & paste
1) Did You make ONE SHEET which has ALL DATA with correct/same layout?
2) Did You make NEW PIVOT as in my sample ~weekly?
3) Which weekly values do You need? You can add more if needed...

 

[bosco_yip]

Adjustment is making as in :

1] B3, formula copy down :

=IFERROR(INT((6+DAY(LOOKUP(9^9,INDIRECT("'"&TEXT(C3,"mmm yy")&"'"&"!A:A"))+1-WEEKDAY(LOOKUP(9^9,INDIRECT("'"&TEXT(C3,"mmm yy")&"'"&"!A:A"))-1)))/7),"")

2] F3, formula copy down :

=IF(E3=0,0,E3/B3)

3] See attached file

Regards