Need a formula to recalculate a deliverable at the end of each week
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
helperfuture week?: populates yes or no so that the "forms expected done" are not recalculated for weeks past
helperweeks left: the number to divide the total forms due by
helpersat?: 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([helpersat?]@row = "yes", [helperfuture week?]@row = "yes"), [Forms remaining to Complete]@row/[helperweeks left]@row)
Is this too complicated or does anyone have advice that could help me? Let me know if I should share more information!
Answers

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([helpersat?]@row, "yes", [helperfuture 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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!