Help with Auto-populating Percentages Based on User Input

Hydestone

Board Regular
Joined
Mar 29, 2010
Messages
124
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
ABCDEFGHIJKL
1project infoTest Job - Draw Schedule Projection *
2nameTest Jobcalculated cellPeriodDrawRetainagePay Amount
3number12345user definedPercentage Amount PercentageWithheld
414.0% $ 40,000 5.00% $ 2,000 $ 38,000
5project start12/31/201928.0% $ 80,000 5.00% $ 4,000 $ 76,000
6project finish11/1/2020314.0% $ 140,000 5.00% $ 7,000 $ 133,000
7pay periods12416.0% $ 160,000 5.00% $ 8,000 $ 152,000
8514.0% $ 140,000 5.00% $ 7,000 $ 133,000
9contract total 1,000,000 numbers are good612.0% $ 120,000 5.00% $ 6,000 $ 114,000
10retainage percentage5.00%710.0% $ 100,000 5.00% $ 5,000 $ 95,000
11reduce retainage at 50%?2.50%88.0% $ 80,000 5.00% $ 4,000 $ 76,000
1296.0% $ 60,000 5.00% $ 3,000 $ 57,000
13billing option (C, F, B, or T)C104.0% $ 40,000 5.00% $ 2,000 $ 38,000
14112.0% $ 20,000 5.00% $ 1,000 $ 19,000
15customfrontbelltail122.0% $ 20,000 5.00% $ 1,000 $ 19,000
16periodCFBTRET retainage release > $ 50,000
1714.0%4.0%4.0%4.0%Totals 1,000,000 $ 1,000,000
1828.0%8.0%8.0%8.0%
19314.0%14.0%14.0%14.0%* Projection based on 75% DD Budget
20416.0%16.0%16.0%16.0%
21514.0%14.0%14.0%14.0%
22612.0%12.0%12.0%12.0%
23710.0%10.0%10.0%10.0%
2488.0%8.0%8.0%8.0%
2596.0%6.0%6.0%6.0%
26104.0%4.0%4.0%4.0%
27112.0%2.0%2.0%2.0%
28122.0%2.0%2.0%2.0%
29
30questions / notes
31do not want to use macros in workbook
32how can I autopopulate the number of periods in (A17:A28) and (G4:15) based on result in cell C7?
33can the percentages in column H autofill based on value in B13 and corresponding % table below (A16:E28)?
34can 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
RangeFormula
G1G1=CONCATENATE(name," - Draw Schedule Projection *")
H4:H15H4=B17
I4:I15I4=contract*H4
J4:J15J4=retainage
K4:K15K4=I4*J4
B7B7=DATEDIF(EOMONTH(B5,-1)+1,EOMONTH(B6,0),"m")+1
C9C9=IF((contract<>I17)," numbers are off"," numbers are good")
I17I17=SUM(I4:I15)
L4:L15L4=I4-K4
L16L16=SUM(K4:K15)
L17L17=SUM(L4:L16)
A18:A28A18=IF((A17<$B$7),A17+1,)
Named Ranges
NameRefers ToCells
contract=Sheet1!$B$9C9, I4:I15
retainage=Sheet1!$B$10J4:J15
 

Attachments

  • sov graph.JPG
    sov graph.JPG
    39.1 KB · Views: 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Hydestone

Board Regular
Joined
Mar 29, 2010
Messages
124
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
ABCDEFGHIJKLMN
1project infoTest Job - Draw Schedule Projection *
2nameTest Jobcalculated cellPeriodDrawRetainagePay Amount
3number12345user definedCumulativePercentage Amount PercentageWithheld
4 $ 20,000 12.0% $ 20,000 5.00% $ 1,000 $ 19,000
5project start12/31/2019 $ 60,000 24.0% $ 40,000 5.00% $ 2,000 $ 38,000
6project finish11/1/2020 $ 120,000 36.0% $ 60,000 5.00% $ 3,000 $ 57,000
7pay periods12 $ 200,000 48.0% $ 80,000 5.00% $ 4,000 $ 76,000
8 $ 300,000 510.0% $ 100,000 5.00% $ 5,000 $ 95,000
9contract total 1,000,000 numbers are good $ 420,000 612.0% $ 120,000 5.00% $ 6,000 $ 114,000
10retainage percentage5.00% $ 560,000 714.0% $ 140,000 5.00% $ 7,000 $ 133,000
11reduce retainage at 50%?2.50% $ 720,000 816.0% $ 160,000 0.00% $ - $ 160,000
12 $ 860,000 914.0% $ 140,000 0.00% $ - $ 140,000
13billing option (C, F, B, or T)T $ 940,000 108.0% $ 80,000 0.00% $ - $ 80,000
14 $ 980,000 114.0% $ 40,000 0.00% $ - $ 40,000
15customfrontbelltail $1,000,000 122.0% $ 20,000 0.00% $ - $ 20,000
16periodCFBTRET retainage release > $ 28,000
1714.0%6.0%2.0%2.0%Totals 1,000,000 $ 1,000,000
1828.0%10.0%4.0%4.0%
19314.0%16.0%8.0%6.0%* Projection based on 75% DD Budget
20416.0%18.0%10.0%8.0%
21514.0%16.0%12.0%10.0%
22612.0%10.0%14.0%12.0%
23710.0%8.0%14.0%14.0%
2488.0%6.0%12.0%16.0%
2596.0%4.0%10.0%14.0%
26104.0%3.0%8.0%8.0%
27112.0%2.0%4.0%4.0%
28122.0%1.0%2.0%2.0%
29100.0%100.0%100.0%100.0%
30
31how can I autopopulate the number of periods in (A17:A28) and (G4:15) based on result in cell C7?
Sheet1
Cell Formulas
RangeFormula
I1I1=CONCATENATE(name," - Draw Schedule Projection *")
I4:I15I4=A17
J4:J15J4=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:K15K4=contract*J4
L4L4=IF((SUM(K4)<(contract/2)),retainage,)
M4:M15M4=K4*L4
L5:L15L5=(IF(G4<(contract/2),retainage,))
B7B7=DATEDIF(EOMONTH(B5,-1)+1,EOMONTH(B6,0),"m")+1
C9C9=IF((contract<>K17)," numbers are off"," numbers are good")
G4G4=SUM(K4)
G5G5=SUM(K4:K5)
G6G6=SUM(K4:K6)
G7G7=SUM(K4:K7)
G8G8=SUM(K4:K8)
G9G9=SUM(K4:K9)
G10G10=SUM(K4:K10)
G11G11=SUM(K4:K11)
G12G12=SUM(K4:K12)
G13G13=SUM(K4:K13)
G14G14=SUM(K4:K14)
G15G15=SUM(K4:K15)
K17K17=SUM(K4:K15)
N4:N15N4=K4-M4
N16N16=SUM(M4:M15)
N17N17=SUM(N4:N16)
A18:A28A18=IF((A17<$B$7),A17+1,)
B29:E29B29=SUM(B17:B28)
Named Ranges
NameRefers ToCells
contract=Sheet1!$B$9C9, K4:L15
retainage=Sheet1!$B$10L4:L15
 

Hydestone

Board Regular
Joined
Mar 29, 2010
Messages
124
There is a graph that changes based on data...there aren't any issues with that.
 

Hydestone

Board Regular
Joined
Mar 29, 2010
Messages
124
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
ABCDEFG
2nameTest Job
3number12345
4
5project start1/1/2020
6project finish12/15/2020
7pay periods12
8
9contract total 2,500,000 numbers are good
10retainage (%)5.00%
11reduce at 50%?YY or N
12optionFC, F, B, or T
13
14
15optionscustomfront loadbelltail loadCumulative Billing
16periodCFBT
1714%6%2%2% $ 150,000
1828%10%4%4% $ 400,000
19314%16%8%6% $ 800,000
20416%18%10%8% $ 1,250,000
21514%16%12%10% $ 1,650,000
22612%10%14%12% $ 1,900,000
23710%8%14%14% $ 2,100,000
2488%6%12%16% $ 2,250,000
2596%4%10%14% $ 2,350,000
26104%3%8%8% $ 2,425,000
27112%2%4%4% $ 2,475,000
28122%1%2%2% $ 2,500,000
29total100%100%100%100%don't edit this
30% off by 0%0%0%0%
draw schedule
Cell Formulas
RangeFormula
C7C7=DATEDIF(EOMONTH(C5,-1)+1,EOMONTH(C6,0),"m")+1
D9D9=IF((contract<>M17),"numbers are off!","numbers are good")
A18:A28A18=IF((AND(A17<$C$7,A17<>0)),A17+1,0)
G17G17=SUM(M4)
G18G18=SUM(M4:M5)
G19G19=SUM(M4:M6)
G20G20=SUM(M4:M7)
G21G21=SUM(M4:M8)
G22G22=SUM(M4:M9)
G23G23=SUM(M4:M10)
G24G24=SUM(M4:M11)
G25G25=SUM(M4:M12)
G26G26=SUM(M4:M13)
G27G27=SUM(M4:M14)
G28G28=SUM(M4:M15)
B29:E29B29=SUM(B17:B28)
B30:E30B30=1-B29
Named Ranges
NameRefers ToCells
contract='draw schedule'!$C$9D9
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A17:A28Cell Value<1textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,575
Members
416,925
Latest member
malamutus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top