Rolling Task Time Average for Tasks Completed with a 90 day Range

laura.davey
laura.davey ✭
edited 03/25/24 in Formulas and Functions

Hello,

My team has a task sheet that outlines all of the recurring tasks to be completed. The sheet is pre-populated for the tasks required for the entire year, with their assigned due dates. As the staff member completes their assigned task, they record the completion date and the time in minutes to complete the task. The time in minutes is then converted to "Actual Time (H)" - actual time in hours. This is used for some other reporting we do.

Based on the actual time in hours to complete the task, I'm trying to determine a rolling average of the time it takes to complete the task over a rolling 90 days. Here's an example.

Task 1 - due 1/4/2024 - completed 1/4/2024 - 1.5 hours actual time

Task 1 - due 1/18/2024 - completed 1/18/2024 - .75 hours actual time

Task 1 - due 2/1/2024 - completed 2/1/2024 - .75 hours actual time

Task 1 - due 2/15/2024 - completed 2/15/2024 - .58 hours actual time

Task 1 - due 2/29/2024 - completed 2/29/2024 - .50 hours actual time

Task 1 - due 3/14/2024 - completed 3/14/2024 - .50 hours actual time

Task 1 - due 3/28/2024 - not due/completed


For task 1 due on 3/28/2024 - I want to calculate the average of the previously completed task in the last 90 days to estimate the amount of time it will take to complete on 3/28. The average is .76 hours of actual time. As you can see this staff member is improving their efficiency with processing this task, so I want the estimated time to complete the task to do a 90 look back to average the task time.

Lastly... since this sheet is built out for the entire year and we use the estimated time to project workload by staff member, I want all subsequent task lines not yet completed to report the same as the task due on 3/28. Once the task due on 3/28 is completed, it would auto-calculate the time for all the tasks in the future.

I've tried AverageIf and College/AVG formula. I have the majority of the formula... just cant get the date range to work with the completed date for the look back.

This formula works in my sheet, but it's not what I'm looking for:

=AVG(COLLECT([Actual Time (H)]:[Actual Time (H)], Task:Task, Task@row, [Completion Date]:[Completion Date], =[Completion Date]@row))

Any thoughts on how I can incorporate the 90-day look back and populate the future estimated time for the task?

· Share on FacebookShare on Twitter

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭✭

    I would try this for your 90-day look back:

    =AVG(COLLECT([Actual Time (H)]:[Actual Time (H)], Task:Task, Task@row, [Completion Date]:[Completion Date], <=TODAY(-90)))

    You may also considering adding a range and criterion to only pull tasks where the actual time is not blank. You wouldn't want a clerical miss to throw off your average, I assume. If so, the formula would be modified as so:

    =AVG(COLLECT([Actual Time (H)]:[Actual Time (H)], Task:Task, Task@row, [Actual Time (H)]:[Actual Time (H)], NOT(ISBLANK(@cell)), [Completion Date]:[Completion Date], <=TODAY(-90)))

    Does this answer your question? This assumes you were wanting to look back 90 days from today's date rather than 90 days from the completion date on that row. If you were just wanting to go back 90 days from due date on that row, your formula would look more like this:

    =AVG(COLLECT([Actual Time (H)]:[Actual Time (H)], Task:Task, Task@row, [Actual Time (H)]:[Actual Time (H)], NOT(ISBLANK(@cell)), [Completion Date]:[Completion Date], <=(DATE(@cell)-90)))

    Hope this helps!:)

    · Share on FacebookShare on Twitter
  • laura.davey
    laura.davey ✭

    Hi Brian,

    I think the last one is the route I want to go. I tried to type it in manually, and got unparseable, but then when i copied/pasted yours directly in, I get #incorrect argument set. What is the @cell supposed to be referencing?

    Thank you!

    · Share on FacebookShare on Twitter
  • brianschmidt
    brianschmidt ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/427141#Comment_427141

    Sorry for the delay. Looks like my notifications weren't set up to notify me of responses on this thread...

    The "@cell" in a criterion range-criterion statement references the criterion range. For example, in the "[Actual Time (H)]:[Actual Time (H)], NOT(ISBLANK(@cell))" portion of the formula above, the @cell references the "[Actual Time (H)]:[Actual Time (H)]" range.

    If it's returning #INCORRECT ARGUMENT SET, it's possible one of your ranges don't match the function type. I would make sure that your actual time column just houses values (aka numbers and not any letters). If it's still throwing an error, let me know. Happy to keep working it out:)

    image.png


    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!