Hello all,
I have a spreadsheet that I use as an ongoing day-to-day task list. Every time I get a new task, I input the date received into Column A, the type of task (I have around five possible tasks that I can receive) into column C, a summary of the task into Column E, My current status in the task in Column F, and a "Completed" box in column G (for this column I have a VBA code that automatically enters a check mark into the cell when I double click and then highlights that row). After a task has been completed, I want to add the completion date into Column B and then custom sort the cells so that all of the highlighted (completed) rows go to the bottom of the list and all of the blank rows (pending) remain at the top of the list.
I am wondering if there is anyway to have a formula that will have an ongoing calculation that takes the average of the difference between the completion date (column B) and the received date (column A) for each different type of task (column F). I would like to stray away from having a formula that I have to drag down every day to do an average calculation for each individual cell. Each day I will add more lines to the list so I do not want a formula that I have to drag down to, say, the 2000th row just so I know that the calculation will run every time I add data.
I do not know on any given day how many requests there will be but I want to be able to look back at any time and see the average number of days that it takes me to complete a certain type of task. If there is a way to see how many requests of a certain task type that I get in any given amount of time that would be awesome too.
Thank you all!
I have a spreadsheet that I use as an ongoing day-to-day task list. Every time I get a new task, I input the date received into Column A, the type of task (I have around five possible tasks that I can receive) into column C, a summary of the task into Column E, My current status in the task in Column F, and a "Completed" box in column G (for this column I have a VBA code that automatically enters a check mark into the cell when I double click and then highlights that row). After a task has been completed, I want to add the completion date into Column B and then custom sort the cells so that all of the highlighted (completed) rows go to the bottom of the list and all of the blank rows (pending) remain at the top of the list.
I am wondering if there is anyway to have a formula that will have an ongoing calculation that takes the average of the difference between the completion date (column B) and the received date (column A) for each different type of task (column F). I would like to stray away from having a formula that I have to drag down every day to do an average calculation for each individual cell. Each day I will add more lines to the list so I do not want a formula that I have to drag down to, say, the 2000th row just so I know that the calculation will run every time I add data.
I do not know on any given day how many requests there will be but I want to be able to look back at any time and see the average number of days that it takes me to complete a certain type of task. If there is a way to see how many requests of a certain task type that I get in any given amount of time that would be awesome too.
Thank you all!
