PDA

View Full Version : how to calculate the weekly average of the last completed week



ffarshadd
06-05-2017, 05:06 AM
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
06-05-2017, 05:28 AM
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
06-05-2017, 05:57 AM
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
06-05-2017, 06:46 AM
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
06-05-2017, 08:07 AM
than you for replying but it didn't work could you please check it

Did you array-enter it?

ffarshadd
06-05-2017, 11:51 AM
Did you array-enter it?

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

Bob Phillips
06-05-2017, 02:38 PM
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
06-05-2017, 02:48 PM
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.