Cumulative target formulas

[malitec]

Hi Gurus

I am working on a spreadsheet (attached) that calculates cumulative targets based on unachieved targets. For example, if week one in April has a forecasted target of 100 and the achieved target is 35, remaining 65 tasks to be divided across the remining weeks in the quarter.
So in week 2, remaining tasks from week 1 (65) to be divided by 11 (remaining weeks) and then add that figure (6) to the week two cumulative target.

The new week 2 Cumulative target will be 100+6.

If we achieve a target of 55 in week 2, the cumulative target for week 2 should reflect this change.

Could you guys look into my frmauls and tell me whats missing. I am not getting the calculation correct.


regards

[Estevaoba]

What would be the expected cumulative target for H11=10?
If it's 96, try this at G10 and copy down:

=IF(H10>=$F$14,"",IF(H10="",SUM(F10,ROUNDUP(SUM($G$9:G9)/J10,0)),SUM(SUM(F$10,-H10),IFERROR(G9/J10,0))))