Taking Weekly Average Every Friday.

Options
Muhammad
Muhammad ✭✭
edited 06/09/22 in Formulas and Functions

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.


Tags:

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Muhammad ✭✭
    edited 06/10/22
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Muhammad ✭✭
    edited 06/10/22
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!