Seat Allocation formula

[MissMoby]

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]

Welcome to the MrExcel forum!

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

Book1
	A	B	C	D	E	F	G	H	I	J
1	Box	Capacity		Position Hierarchy	Count		A	B	C	D
2	A	300		Head	250		250	0	0	0
3	B	250		Managers	360		50	250	60	0
4	C	100		Staff	120		0	0	40	80
5	D	80
Sheet4

Cell Formulas
Range	Formula
G2	=MEDIAN($E2-SUM($F2:F2),VLOOKUP(G$1,$A$2:$B$5,2,0)-SUM(G$1:G1),0)

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

 

[Eric W]

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))

 

[MissMoby]

Super thank you Mr. Eric W!!!! I'm super happy!!!!! =)

 

[MissMoby]

Hats off! Super thank you!! =)

 

[Eric W]

Glad to help!