Need a formula to recalculate a deliverable at the end of each week

bridgets

My project is that a certain number of Forms needs to be created within the next 11 weeks. We have a group of people working on the project and right now I have the weekly goal for form creation divided by the weeks left. I want the formula to recalculate the weekly goal for the subsequent week at the end of each current week so that the goal number is accurate based on if we don't meet our goal in a given week. I've created some helper columns and think I'm close but just not able to get it.

Here are the rows i'm working with:

Forms expected done: the number that needs to get done this week (calculated using the formula)

Forms done: forms done for that week

forms remaining to complete: the number of forms still needing to be created

helper-future week?: populates yes or no so that the "forms expected done" are not recalculated for weeks past

helper-weeks left: the number to divide the total forms due by

helper-sat?: recalculate based on whether it is sat (ie- the end of the work week)

So, essentially, the formula would do this: if it is Saturday, the formula will divide the forms remaining by the number of weeks left and populate that divided number for all upcoming weeks

=IF(AND([helper-sat?]@row = "yes", [helper-future week?]@row = "yes"), [Forms remaining to Complete]@row/[helper-weeks left]@row)

Is this too complicated or does anyone have advice that could help me? Let me know if I should share more information!

Jeff M.

Have you explored using the automation feature to trigger a few events on Saturday? One could help populate the "forms expected to be done by saying if "so and so column(s)" are blank, change "future week" column to yes or no.

My thoughts would be to create additional columns at the end for your calculations.

Total Remaining Column: COUNTIF(Status:Status, "Not Complete")

OR COUNTIFS([helper-sat?]@row, "yes", [helper-future week?]@row, "yes".... if you wanted to keep your method.

Goal Column: Use the first 11 cells with each one representing a week. Calculate your beginning goal for the first week and enter in the first row cell. The second cell would be = ROUND(Total Remaining1 / 10). The third cell ROUND(Total Remaining1 / 9)

Hopefully this could can help you get started.