Seat Allocation formula

MissMoby

New Member
Joined
Oct 9, 2019
Messages
3
Hi, need help on how to use Excel to meet below requirement. Below sample is just small population of total seatings so would need to automate. Appreciate your help please, thank you!!! =)

Requirements:
1. Allocate the Box capacity following the Position hierarchy, such that "Head" starts to seat on Box A.
2. If there is remaining in previous Box, then allocate to next level and so on.

Box & Capacity
A - 300
B - 250
C - 100
D - 80

Position Hierarchy & Count
Head - 250
Managers - 360
Staff - 120
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
Welcome to the MrExcel forum!

I don't know what your layout looks like, but try this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Box</td><td style=";">Capacity</td><td style="text-align: right;;"></td><td style=";">Position Hierarchy</td><td style=";">Count</td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">B</td><td style=";">C</td><td style=";">D</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A</td><td style="text-align: right;;">300</td><td style="text-align: right;;"></td><td style=";">Head</td><td style="text-align: right;;">250</td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">B</td><td style="text-align: right;;">250</td><td style="text-align: right;;"></td><td style=";">Managers</td><td style="text-align: right;;">360</td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td><td style="text-align: right;;">250</td><td style="text-align: right;;">60</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">C</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style=";">Staff</td><td style="text-align: right;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">40</td><td style="text-align: right;;">80</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">D</td><td style="text-align: right;;">80</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;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">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: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=MEDIAN(<font color="Blue">$E2-SUM(<font color="Red">$F2:F2</font>),VLOOKUP(<font color="Red">G$1,$A$2:$B$5,2,0</font>)-SUM(<font color="Red">G$1:G1</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />

The empty column in F is required. Put the formula in G2, then copy across to J2, then down to J4.
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
After a bit more consideration, this somewhat simpler formula works just the same:

=MIN($E2-SUM($F2:F2),VLOOKUP(G$1,$A$2:$B$5,2,0)-SUM(G$1:G1))
 

Forum statistics

Threads
1,077,784
Messages
5,336,330
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top