how to calculate the weekly average of the last completed week

[ffarshadd]

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.

[Bob Phillips]

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

[ffarshadd]

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

than you for replying but it didn't work could you please check it

[mdmackillop]

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.

[Bob Phillips]

than you for replying but it didn't work could you please check it

Did you array-enter it?

[ffarshadd]

Did you array-enter it?

i don't know how to do that i just copy that to an empty cell

[Bob Phillips]

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.

[Bob Phillips]

You could also use this


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

just copy it to the other cells.