I don't even know how to title this question!

[jcaintn]

I'm stumped. First, I'm an Architect, so I don't work in Excel every day, but I need help with a problem. I want to set up a worksheet that yields the following result... These are the known factors:

Number of Parking Spaces available, % desired of one-bedroom units, % desired of two-bedroom units & % desired of three-bedroom units. Each bedroom requires one parking space (one-bedroom unit = 1 parking space, two-bedroom unit = 2 parking spaces, three-bedroom unit = 3 parking spaces).

I want the worksheet to convert the % of each unit type into the # of required parking spaces, then work that into the number of available spaces. Once I've entered the % of each unit type, I want the result to show me how many units of each type I can have as I change the number of available parking spaces. Thanks in advance for your help/expertise.

John

 

[Scott R]

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";"># spaces</td><td style="text-align: right;color: #0000FF;;">35</td><td style="text-align: right;;">Spaces/BR</td><td style="text-align: right;;">% of spaces</td><td style="text-align: right;;">Total BR's</td><td style="text-align: right;;">BR's/Unit</td><td style="text-align: right;;">Units</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">% 1BR units</td><td style="text-align: right;color: #0000FF;;">50.00%</td><td style="text-align: right;color: #0000FF;;">1</td><td style="text-align: right;;">28.57%</td><td style="text-align: right;;">10.000</td><td style="text-align: right;color: #0000FF;;">1</td><td style="text-align: right;;">10.000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">% 2BR units</td><td style="text-align: right;color: #0000FF;;">25.00%</td><td style="text-align: right;color: #0000FF;;">2</td><td style="text-align: right;;">28.57%</td><td style="text-align: right;;">10.000</td><td style="text-align: right;color: #0000FF;;">2</td><td style="text-align: right;;">5.000</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">% 3BR units</td><td style="text-align: right;;">25.00%</td><td style="text-align: right;color: #0000FF;;">3</td><td style="text-align: right;;">42.86%</td><td style="text-align: right;;">15.000</td><td style="text-align: right;color: #0000FF;;">3</td><td style="text-align: right;;">5.000</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">35.000</td><td style="text-align: right;;"></td><td style="text-align: right;;">20.000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=1-B2-B3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=(<font color="Blue">B2*C2</font>)/SUMPRODUCT(<font color="Blue">$B$2:$B$4,$C$2:$C$4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=$B$1*D2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=(<font color="Blue">B3*C3</font>)/SUMPRODUCT(<font color="Blue">$B$2:$B$4,$C$2:$C$4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=$B$1*D3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=(<font color="Blue">B4*C4</font>)/SUMPRODUCT(<font color="Blue">$B$2:$B$4,$C$2:$C$4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=$B$1*D4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=SUM(<font color="Blue">E2:E4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=E2/F2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=E3/F3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=E4/F4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G5</th><td style="text-align:left">=SUM(<font color="Blue">G2:G4</font>)</td></tr></tbody></table></td></tr></table><br />

 

[jcaintn]

Thanks Scott. This helps a great deal. I really like how you can vary the # of parking spaces based on the bedroom count as some municipalities require different amounts.

 

[Andrew Fergus]

The formula in E2 (copied down) could be amended to avoid giving rounding problems, to this:

=ROUND($B$1*D2/C2,0)*C2

If you try the value of 100 available spaces and the same percentages you end up with 28.5 1 bedroom units or you may end up with a number of carparks allocated to a 2 or 3 bedroom unit that is not the correct multiple (e.g. 80 spaces at 25% for 3 bedroom units yields a carpark allocation that is not a mulitple of 3).

In which case you may end up with 1 or 2 extra carparks being allocated or 1 or 2 short - which you could force back through the number of single bedroom units.

Andrew

 

[jcaintn]

Thanks to you Andrew, as well. I can see how the rounding could become an issue.