Taking Weekly Average Every Friday.

Muhammad

Hi all!

I have a spreadsheet, on this sheet I have some values I would like the average of as well as dates assigned to these values. Taking the average is not the only thing I need to do, I need to take the average of these values, grouped by week, from Friday to Thursday. Meaning I have to find a way to group the values by week given just their dates, then I must further modify it so the 7 days recorded are from Friday to Thursday, I THEN need to return the average of those values. If anyone could suggest to me any kind of way I can do this, I would really appreciate it, any help is useful. I will provide some mock data below, as well as some mock results that would be ideal.

*Note: The average is the days to complete over the amount of days recorded in that week, shown as the count.

Paul Newcome

I see the issue. It looks like the structure has changed.

You now need to use this:

=AVG(COLLECT([Days to Complete]:[Days to Complete], [Week Of]:[Week Of], @cell = [Week Of]@row))

Paul Newcome

Try an AVG/COLLECT combo.

=AVG(COLLECT({Days To Complete}, {Dates}, AND(@cell >= [Week of]@row - 7, @cell <= [Week of]@row)))

NOTE: The above is assuming the "Week of" column dates are the ending Friday.

Muhammad

https://community.smartsheet.com/discussion/comment/332542#Comment_332542

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

Make sure the Date Completed column is set as a date type column and make sure that the entries are actually dates and not just text values that look like dates.

Muhammad

@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

I see the issue. It looks like the structure has changed.

You now need to use this:

=AVG(COLLECT([Days to Complete]:[Days to Complete], [Week Of]:[Week Of], @cell = [Week Of]@row))

Muhammad

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

Paul Newcome

Happy to help. 👍️