Taking Weekly Average Every Friday.

Hi @Paul Newcome Thank you much for your response! I am currently trying this formula on one sheet (both the dates and the days to complete are on the same sheet), so the formula you wrote is returning and "unparseable" error for me. I have tried to modify it it to the following:

=AVG(COLLECT([Days to Complete]:[Days to Complete], [Date Completed]:[Date Completed], AND(@cell >= [Week Of]@row - 7, @cell <= [Week Of]@row)))

but now I am getting a "divide by 0" error.

Here is a screen shot of the dummy data I am using, please ignore the irrelevant columns, I have been trying out other solutions in the meantime.

Thank you again, this is a lot of help!!!

***Note: In the end the solution will be implemented cross referencing sheets, as in the final solution will grab the "dates" and "days to complete" from another sheet and display the averages on a separate sheet, however before implementing the actual solution I am just testing it on the same sheet.

@Paul Newcome I have checked and the "Date Completed" column is a date column and all values are indeed dates. To give an update on my progress thus far, I have been able to now assign a "Week Of" to every date and value, now I am currently trying to compute the average based on the "Week Of" value. Basically I want to take the average of all values with corresponding "Week Of" values, but I'm in a little bit of a stump as to how to do this w/o the use of variables. Let me show you the updated progress as well as the formula I made to assign a "Week Of" value to rows.

So now, I am stuck with this formula, I have the span to take the average, as well as the span for the criterion, where I'm stuck is what the syntax of the criterion, I don't know how to group them by identical "Week Of" values. Thank you again for all the help Paul, what you sent gave me a lot of insights and ideas as to how to approach this problem!

***Edit: Here is my Week Of formula as well as my average formula thus far:

Week Of:

=IF((IF(WEEKDAY([Date Completed]@row) = 6, WEEKNUMBER([Date Completed]@row) + 1, WEEKNUMBER([Date Completed]@row))) > 19, DATE(2020, 12, 30) + ((IF(WEEKDAY([Date Completed]@row) = 6, WEEKNUMBER([Date Completed]@row) + 1, WEEKNUMBER([Date Completed]@row))) * 7 - 4), DATE(2022, 1, 4) + (((IF(WEEKDAY([Date Completed]@row) = 6, WEEKNUMBER([Date Completed]@row) + 1, WEEKNUMBER([Date Completed]@row))) - 1) * 7 - 4))

Average:

=AVG(COLLECT([Days to Complete]:[Days to Complete], [Week Of]:[Week Of], AND()))

@Paul Newcome Thank you so much, this solution worked perfectly!