Help with Auto-populating Percentages Based on User Input

[Hydestone]

Some more questions related to the draw schedule, which I'd like to create without using macros if possible.

1. how can I autopopulate the number of periods in (A17:A28) and (G4:15) based on result in cell C7?
2. can the percentages in column H autofill based on value in B13 and corresponding % table below (A16:E28)?
3. can the percentage in column J change to 0% for the next period (column G) when the cumulative pay amount in L becomes greater than 50% of I16?

Anticipated Draw Schedule - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	project info						Test Job - Draw Schedule Projection *
2	name	Test Job		calculated cell			Period	Draw		Retainage		Pay Amount
3	number	12345		user defined				Percentage	Amount	Percentage	Withheld
4							1	4.0%	$ 40,000	5.00%	$ 2,000	$ 38,000
5	project start	12/31/2019					2	8.0%	$ 80,000	5.00%	$ 4,000	$ 76,000
6	project finish	11/1/2020					3	14.0%	$ 140,000	5.00%	$ 7,000	$ 133,000
7	pay periods	12					4	16.0%	$ 160,000	5.00%	$ 8,000	$ 152,000
8							5	14.0%	$ 140,000	5.00%	$ 7,000	$ 133,000
9	contract total	1,000,000	numbers are good				6	12.0%	$ 120,000	5.00%	$ 6,000	$ 114,000
10	retainage percentage	5.00%					7	10.0%	$ 100,000	5.00%	$ 5,000	$ 95,000
11	reduce retainage at 50%?	2.50%					8	8.0%	$ 80,000	5.00%	$ 4,000	$ 76,000
12							9	6.0%	$ 60,000	5.00%	$ 3,000	$ 57,000
13	billing option (C, F, B, or T)	C					10	4.0%	$ 40,000	5.00%	$ 2,000	$ 38,000
14							11	2.0%	$ 20,000	5.00%	$ 1,000	$ 19,000
15		custom	front	bell	tail		12	2.0%	$ 20,000	5.00%	$ 1,000	$ 19,000
16	period	C	F	B	T		RET				retainage release >	$ 50,000
17	1	4.0%	4.0%	4.0%	4.0%			Totals	1,000,000			$ 1,000,000
18	2	8.0%	8.0%	8.0%	8.0%
19	3	14.0%	14.0%	14.0%	14.0%		* Projection based on 75% DD Budget
20	4	16.0%	16.0%	16.0%	16.0%
21	5	14.0%	14.0%	14.0%	14.0%
22	6	12.0%	12.0%	12.0%	12.0%
23	7	10.0%	10.0%	10.0%	10.0%
24	8	8.0%	8.0%	8.0%	8.0%
25	9	6.0%	6.0%	6.0%	6.0%
26	10	4.0%	4.0%	4.0%	4.0%
27	11	2.0%	2.0%	2.0%	2.0%
28	12	2.0%	2.0%	2.0%	2.0%
29
30	questions / notes
31	do not want to use macros in workbook
32	how can I autopopulate the number of periods in (A17:A28) and (G4:15) based on result in cell C7?
33	can the percentages in column H autofill based on value in B13 and corresponding % table below (A16:E28)?
34	can the percentage in column J change to 0% for the next period (column G) when the cumulative pay amount in L becomes greater than 50% of I16?
35
36
37
38
Sheet1

Cell Formulas
Range	Formula
G1	G1	=CONCATENATE(name," - Draw Schedule Projection *")
H4:H15	H4	=B17
I4:I15	I4	=contract*H4
J4:J15	J4	=retainage
K4:K15	K4	=I4*J4
B7	B7	=DATEDIF(EOMONTH(B5,-1)+1,EOMONTH(B6,0),"m")+1
C9	C9	=IF((contract<>I17)," numbers are off"," numbers are good")
I17	I17	=SUM(I4:I15)
L4:L15	L4	=I4-K4
L16	L16	=SUM(K4:K15)
L17	L17	=SUM(L4:L16)
A18:A28	A18	=IF((A17<$B$7),A17+1,)

Named Ranges
Name	Refers To	Cells
contract	=Sheet1!$B$9	C9, I4:I15
retainage	=Sheet1!$B$10	J4:J15

 

[Hydestone]

Hi All: I figured out questions 2 and 3, see below. I am able to get it working if there are 12 pay periods...basically by hard coding.

If for some reason, there are only 10 pay periods, all the percentages will need to be spread out across 10 pay periods and still total 100%.

Any chance that is possible without macros?

Anticipated Draw Schedule - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	project info								Test Job - Draw Schedule Projection *
2	name	Test Job		calculated cell					Period	Draw		Retainage		Pay Amount
3	number	12345		user defined			Cumulative			Percentage	Amount	Percentage	Withheld
4							$ 20,000		1	2.0%	$ 20,000	5.00%	$ 1,000	$ 19,000
5	project start	12/31/2019					$ 60,000		2	4.0%	$ 40,000	5.00%	$ 2,000	$ 38,000
6	project finish	11/1/2020					$ 120,000		3	6.0%	$ 60,000	5.00%	$ 3,000	$ 57,000
7	pay periods	12					$ 200,000		4	8.0%	$ 80,000	5.00%	$ 4,000	$ 76,000
8							$ 300,000		5	10.0%	$ 100,000	5.00%	$ 5,000	$ 95,000
9	contract total	1,000,000	numbers are good				$ 420,000		6	12.0%	$ 120,000	5.00%	$ 6,000	$ 114,000
10	retainage percentage	5.00%					$ 560,000		7	14.0%	$ 140,000	5.00%	$ 7,000	$ 133,000
11	reduce retainage at 50%?	2.50%					$ 720,000		8	16.0%	$ 160,000	0.00%	$ -	$ 160,000
12							$ 860,000		9	14.0%	$ 140,000	0.00%	$ -	$ 140,000
13	billing option (C, F, B, or T)	T					$ 940,000		10	8.0%	$ 80,000	0.00%	$ -	$ 80,000
14							$ 980,000		11	4.0%	$ 40,000	0.00%	$ -	$ 40,000
15		custom	front	bell	tail		$1,000,000		12	2.0%	$ 20,000	0.00%	$ -	$ 20,000
16	period	C	F	B	T				RET				retainage release >	$ 28,000
17	1	4.0%	6.0%	2.0%	2.0%					Totals	1,000,000			$ 1,000,000
18	2	8.0%	10.0%	4.0%	4.0%
19	3	14.0%	16.0%	8.0%	6.0%				* Projection based on 75% DD Budget
20	4	16.0%	18.0%	10.0%	8.0%
21	5	14.0%	16.0%	12.0%	10.0%
22	6	12.0%	10.0%	14.0%	12.0%
23	7	10.0%	8.0%	14.0%	14.0%
24	8	8.0%	6.0%	12.0%	16.0%
25	9	6.0%	4.0%	10.0%	14.0%
26	10	4.0%	3.0%	8.0%	8.0%
27	11	2.0%	2.0%	4.0%	4.0%
28	12	2.0%	1.0%	2.0%	2.0%
29		100.0%	100.0%	100.0%	100.0%
30
31	how can I autopopulate the number of periods in (A17:A28) and (G4:15) based on result in cell C7?
Sheet1

Cell Formulas
Range	Formula
I1	I1	=CONCATENATE(name," - Draw Schedule Projection *")
I4:I15	I4	=A17
J4:J15	J4	=IF(($B$13=$B$16),B17,(IF(($B$13=$C$16),C17,(IF(($B$13=$D$16),D17,(IF(($B$13=$E$16),E17,"X")))))))
K4:K15	K4	=contract*J4
L4	L4	=IF((SUM(K4)<(contract/2)),retainage,)
M4:M15	M4	=K4*L4
L5:L15	L5	=(IF(G4<(contract/2),retainage,))
B7	B7	=DATEDIF(EOMONTH(B5,-1)+1,EOMONTH(B6,0),"m")+1
C9	C9	=IF((contract<>K17)," numbers are off"," numbers are good")
G4	G4	=SUM(K4)
G5	G5	=SUM(K4:K5)
G6	G6	=SUM(K4:K6)
G7	G7	=SUM(K4:K7)
G8	G8	=SUM(K4:K8)
G9	G9	=SUM(K4:K9)
G10	G10	=SUM(K4:K10)
G11	G11	=SUM(K4:K11)
G12	G12	=SUM(K4:K12)
G13	G13	=SUM(K4:K13)
G14	G14	=SUM(K4:K14)
G15	G15	=SUM(K4:K15)
K17	K17	=SUM(K4:K15)
N4:N15	N4	=K4-M4
N16	N16	=SUM(M4:M15)
N17	N17	=SUM(N4:N16)
A18:A28	A18	=IF((A17<$B$7),A17+1,)
B29:E29	B29	=SUM(B17:B28)

Named Ranges
Name	Refers To	Cells
contract	=Sheet1!$B$9	C9, K4:L15
retainage	=Sheet1!$B$10	L4:L15

 

[Hydestone]

There is a graph that changes based on data...there aren't any issues with that.

 

[Hydestone]

I've got this one mostly worked out. The only thing to figure out is how to change the percentages in the table based on the number of periods. Basically, the data is based on 3 standard curve types...front loaded, even bell, tail loaded. So if there are 12 periods, the percentages would get distributed accordingly. Or if there were 7, the percentages would get distributed across 7 periods which would total 100%. Is this possible without code?

Anticipated Draw Schedule - Copy.xlsx
	A	B	C	D	E	F	G
2	name		Test Job
3	number		12345
4
5	project start		1/1/2020
6	project finish		12/15/2020
7	pay periods		12
8
9	contract total		2,500,000	numbers are good
10	retainage (%)		5.00%
11	reduce at 50%?		Y	Y or N
12	option		F	C, F, B, or T
13
14
15	options	custom	front load	bell	tail load		Cumulative Billing
16	period	C	F	B	T
17	1	4%	6%	2%	2%		$ 150,000
18	2	8%	10%	4%	4%		$ 400,000
19	3	14%	16%	8%	6%		$ 800,000
20	4	16%	18%	10%	8%		$ 1,250,000
21	5	14%	16%	12%	10%		$ 1,650,000
22	6	12%	10%	14%	12%		$ 1,900,000
23	7	10%	8%	14%	14%		$ 2,100,000
24	8	8%	6%	12%	16%		$ 2,250,000
25	9	6%	4%	10%	14%		$ 2,350,000
26	10	4%	3%	8%	8%		$ 2,425,000
27	11	2%	2%	4%	4%		$ 2,475,000
28	12	2%	1%	2%	2%		$ 2,500,000
29	total	100%	100%	100%	100%		don't edit this
30	% off by	0%	0%	0%	0%
draw schedule

Cell Formulas
Range	Formula
C7	C7	=DATEDIF(EOMONTH(C5,-1)+1,EOMONTH(C6,0),"m")+1
D9	D9	=IF((contract<>M17),"numbers are off!","numbers are good")
A18:A28	A18	=IF((AND(A17<$C$7,A17<>0)),A17+1,0)
G17	G17	=SUM(M4)
G18	G18	=SUM(M4:M5)
G19	G19	=SUM(M4:M6)
G20	G20	=SUM(M4:M7)
G21	G21	=SUM(M4:M8)
G22	G22	=SUM(M4:M9)
G23	G23	=SUM(M4:M10)
G24	G24	=SUM(M4:M11)
G25	G25	=SUM(M4:M12)
G26	G26	=SUM(M4:M13)
G27	G27	=SUM(M4:M14)
G28	G28	=SUM(M4:M15)
B29:E29	B29	=SUM(B17:B28)
B30:E30	B30	=1-B29

Named Ranges
Name	Refers To	Cells
contract	='draw schedule'!$C$9	D9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A17:A28	Cell Value	<1	text	NO