Modify DelData Formula and maintain evenly spread calculations across forumla

[JRP Excel Guy]

Hello!
JeteMc provided me with some excellent help on my last post while I was trying to work on our payment schedule for our projects. The formula provided gave us the ability to auto calculate the payments with the ability to remove lines as needed without affecting the formula. Thank you again JeteMc!

Now I am being asked to modify the formula but in a way that is really complicating everything. Here are the parameters we are trying to reach:
- C7 & D7 need to match
- C24 & D24 need to be 5% of D25
- C8:C17 need to be evenly divided
- We also need to maintain the ability to remove rows while having the formulas readjust automatically.

I have tried to work this out for a week or so now without any luck. Any help would be greatly appreciated so thank you very much in advance!!!!!

[JeteMc]

This seems to work, although I have done only limited testing to removing and then reapplying a row.
1. C9 is populated using: =[@Budget]
2. C10:C25 are populated using: =ROUNDUP((D$27-SUM($D$9,D$26))/SUMPRODUCT(--(LEFT([WHOLE HOUSE or ADDITION],8)="Upon the")),2)
3. C26 is populated using: =D27-SUM(Table1[Payments])
4. C27 is populated using: =SUM(C9:C26)
Note that the table is set =$A$8:$D$25
Let us know if you have any questions.

[JeteMc]

Found a couple of issues when adding a new row and believe that the following will resolve the issue:
1. Modify the formula in the # column to read: =SUM(MAX(A$8:INDEX(A:A,ROW()-1)),1)
2. Modify the formula in the payments column to read: =IF([@['#]]=1,[@Budget],ROUNDUP((D$28-SUM($D$9,D$27))/SUMPRODUCT(--(LEFT([WHOLE HOUSE or ADDITION],8)="Upon the")),2))
Note that the Payments column will not update until a "Upon the..." statement is placed in the Whole House or Addition column.
Let us know if you have any questions.

[JRP Excel Guy]

Excellent! This works perfectly!!

I must say, what a formula! Very impressive to see what Excel can do and what you, JeteMc are capable of! Thank you so much for spending your time to help me out with this again!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.