I'll attached the file.this file will be updated every day. i wanna have the last weekly average but not last 7 days. current week shouldn't be counted. it must be an average from previous Monday to previous Friday i mean the last completed week.
I'll attached the file.this file will be updated every day. i wanna have the last weekly average but not last 7 days. current week shouldn't be counted. it must be an average from previous Monday to previous Friday i mean the last completed week.
Try this array formula
=AVERAGE(IF(($A$2:$A$2726>=--(TEXT(TODAY()-WEEKDAY(TODAY(),2)-6,"yyyymmdd")))*($A$2:$A$2726<=--(TEXT(TODAY()-WEEKDAY(TODAY(),2)-2,"yyyymmdd"))),$B$2:$B$2726))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
The values in column A are not dates. You should convert these to dates and use a format of yyyymmdd if you want that appearance together with Date functionality.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
You put the formula in the first relevant cell, and instead of hitting Enter, you hit Ctrl-Shift-Enter all together. You will then see Excel wrap curly brackets around it in the formula bar. Then just drag-copy that cell to the rest.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
You could also use this
just copy it to the other cells.=SUMPRODUCT(($A$2:$A$2726>=--(TEXT(TODAY()-WEEKDAY(TODAY(),2)-6,"yyyymmdd")))*($A$2:$A$2726<=--(TEXT(TODAY()-WEEKDAY(TODAY(),2)-2,"yyyymmdd")))*($B$2:$B$2726))/ SUMPRODUCT(($A$2:$A$2726>=--(TEXT(TODAY()-WEEKDAY(TODAY(),2)-6,"yyyymmdd")))*($A$2:$A$2726<=--(TEXT(TODAY()-WEEKDAY(TODAY(),2)-2,"yyyymmdd"))))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber