mulitpal formular to choose a value to = %

[Budgie01]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>Columns C,D & E all have formulas, except cell D21, Currently I manually guess a number, my target is for cell C22 (a percent formula) to = 20%, if the answer in cell C22 is 19.4% I manually input a slightly higher value in cel D21 until cell C22 = 20%. Not sure if it’s possible to have a formula so that I can choose a % (having a drop down with a range of percents) which in turn automatically inputs the correct value in cell D21<o></o>
</o><o></o>
<o></o>
<o>Excel 2010
<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center">No.</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">No. of</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold">Excluding</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Including </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Size of system in kWs</TD><TD style="BORDER-BOTTOM: black 1px solid">of panels</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; FONT-WEIGHT: bold">STC's</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold">GST</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">GST</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; COLOR: #0070c0; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold">1.90</TD><TD style="TEXT-ALIGN: center; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold">10</TD><TD style="TEXT-ALIGN: center; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold">101</TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid">4,858.79 </TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5,344.67 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="BORDER-LEFT: black 1px solid">STCs</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">- 2,203.64 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid">- 2,424.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="BORDER-LEFT: black 1px solid">Transport</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">- </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="BORDER-LEFT: black 1px solid">Install Price</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">1,000.00 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid">1,100.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="BORDER-LEFT: black 1px solid">Cost</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid">3,655.16 </TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">4,020.67 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c4d79b">GP % / Profit</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c4d79b"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c4d79b">25.8%</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #c4d79b">2,034.84 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #c4d79b; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c4d79b">Mark up % / Selling price (RRP)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c4d79b"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c4d79b">34.7%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c4d79b; COLOR: #0070c0; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold">5,690.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c4d79b; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">6,260.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2">GP 20% / Profit</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2">20%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; FONT-WEIGHT: bold">20.0%</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; FONT-WEIGHT: bold">1,465.84 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD style="BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2">Mark-up % / Discounted price</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2">25.0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: black 1px solid">5,121.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">5,630.00 </TD></TR></TBODY></TABLE>

Solar costings​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #bbb 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E15</TH><TD style="TEXT-ALIGN: left">=+D10</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E16</TH><TD style="TEXT-ALIGN: left">=IF(D10="","",-C15*C5)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E18</TH><TD style="TEXT-ALIGN: left">=IF(D10="","",VLOOKUP(A15,A30:B39,2,1))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E19</TH><TD style="TEXT-ALIGN: left">=SUM(E15:E18)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B15</TH><TD style="TEXT-ALIGN: left">=VLOOKUP($A$15,'STC tables'!$E$8:$G$27,COLUMN(C2),0)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C15</TH><TD style="TEXT-ALIGN: left">=VLOOKUP($A$15,'STC tables'!$E$8:$G$27,COLUMN(B2),0)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D15</TH><TD style="TEXT-ALIGN: left">=IF(E15="","",+E15-E15/11)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D16</TH><TD style="TEXT-ALIGN: left">=IF(D10="","",+E16-E16/11)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D17</TH><TD style="TEXT-ALIGN: left">=+E17-E17/11</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D18</TH><TD style="TEXT-ALIGN: left">=IF(D10="","",+E18-E18/11)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D19</TH><TD style="TEXT-ALIGN: left">=SUM(D15:D18)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D20</TH><TD style="TEXT-ALIGN: left">=+D21-D19</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E21</TH><TD style="TEXT-ALIGN: left">=ROUND((+D21*C4+D21),-1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C20</TH><TD style="TEXT-ALIGN: left">=IF(D15="","",1-(D15+D17+D18)/(D21-D16))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C21</TH><TD style="TEXT-ALIGN: left">=IF(D15="","",(D21-D16)/(D15+D17+D18)-1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C22</TH><TD style="TEXT-ALIGN: left">=IF(D15="","",1-(D15+D17+D18)/(D23-D16))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C23</TH><TD style="TEXT-ALIGN: left">=IF(D15="","",(D23-D16)/(D15+D17+D18)-1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D22</TH><TD style="TEXT-ALIGN: left">=+D23-D19</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>D23</TH><TD style="TEXT-ALIGN: left">=-D21*C$2+D21</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E23</TH><TD style="TEXT-ALIGN: left">=ROUND((-E21*C$2+E21),-1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
</o>

 

[AlphaFrog]

Are you familiar with the Excel Solver tool? It will solve for the value in D21 based on C22=20% .

Excel Solver tool

 

[Peter_SSs]

Not all that clear since ..

a) You have formulas referring to cells not shown (eg C2, C4, D10) and we don't know what is in them.

b) You say you have, or want to have, a drop-down with percentages but yoiu haven't said which cell that is or would be. I have assumed that is B22.

I have taken a guess at some of the cells not shown, so try this in D21. Then try changing B22. If I have guessed correctly, C22 should change to match B22.

=(D15+D17+D18+D16*(1-B22))/(1-B22)/(1-C2)

 

[Budgie01]

Hi

Thank you Peter, that been annoying me for months, greatly appreciate your help.

Thank you Mick