title: IF formula for splitting queuing system
body: <p>I have this table that has been inputted manually:</p>
  
  <pre><code>LINE ¦  ROOM A¦ ROOM B¦  ROOM C¦    ROOM D¦ ROOM E¦ ROOM F¦ 
  1    ¦    1   ¦    0  ¦    0   ¦    0     ¦    0  ¦    0
  2    ¦    2   ¦    0  ¦    0   ¦    0     ¦    0  ¦    0
  3    ¦    3   ¦    0  ¦    0   ¦    0     ¦    0  ¦    0
  4    ¦    4   ¦    0  ¦    0   ¦    0     ¦    0  ¦    0
  5    ¦    5   ¦    0  ¦    0   ¦    0     ¦    0  ¦    0
  6    ¦    6   ¦    0  ¦    0   ¦    0     ¦    0  ¦    0
  7    ¦    7   ¦    0  ¦    0   ¦    0     ¦    0  ¦    0
  8    ¦    8   ¦    0  ¦    0   ¦    0     ¦    0  ¦    0
  9    ¦    5   ¦    4  ¦    0   ¦    0     ¦    0  ¦    0
  10   ¦    5   ¦    5  ¦    0   ¦    0     ¦    0  ¦    0
  11   ¦    6   ¦    5  ¦    0   ¦    0     ¦    0  ¦    0
  12   ¦    6   ¦    6  ¦    0   ¦    0     ¦    0  ¦    0
  13   ¦    7   ¦    6  ¦    0   ¦    0     ¦    0  ¦    0
  14   ¦    7   ¦    7  ¦    0   ¦    0     ¦    0  ¦    0
  15   ¦    8   ¦    7  ¦    0   ¦    0     ¦    0  ¦    0
  16   ¦    8   ¦    8  ¦    0   ¦    0     ¦    0  ¦    0
  17   ¦    6   ¦    6  ¦    5   ¦    0     ¦    0  ¦    0
  18   ¦    6   ¦    6  ¦    6   ¦    0     ¦    0  ¦    0
  19   ¦    7   ¦    6  ¦    6   ¦    0     ¦    0  ¦    0
  20   ¦    7   ¦    7  ¦    6   ¦    0     ¦    0  ¦    0
  21   ¦    7   ¦    7  ¦    7   ¦    0     ¦    0  ¦    0
  22   ¦    8   ¦    7  ¦    7   ¦    0     ¦    0  ¦    0
  23   ¦    8   ¦    8  ¦    7   ¦    0     ¦    0  ¦    0
  24   ¦    8   ¦    8  ¦    8   ¦    0     ¦    0  ¦    0
  25   ¦    7   ¦    6  ¦    6   ¦    6     ¦    0  ¦    0
  26   ¦    7   ¦    7  ¦    6   ¦    6     ¦    0  ¦    0
  27   ¦    7   ¦    7  ¦    7   ¦    6     ¦    0  ¦    0
  28   ¦    7   ¦    7  ¦    7   ¦    7     ¦    0  ¦    0
  29   ¦    8   ¦    7  ¦    7   ¦    7     ¦    0  ¦    0
  30   ¦    8   ¦    8  ¦    7   ¦    7     ¦    0  ¦    0
  31   ¦    8   ¦    8  ¦    8   ¦    7     ¦    0  ¦    0
  32   ¦    8   ¦    8  ¦    8   ¦    8     ¦    0  ¦    0
  </code></pre>
  
  <p>What i'm trying to do is look at the "LINE" column and placing everyone in that column in to "ROOM A" until ROOM A is full (when it hits 8 people). When the 9th person joins the "LINE", the groups are then split into two rooms (ROOM A and ROOM B) and as each person joins the line they get added to each room until each room is full at 8 people again, in this case when the 16th person joins the line the two rooms are full.</p>
  
  <p>If a 17th person joins the line then another room opens and all 17 people are split across the rooms, the people are placed into all 3 rooms until all rooms are full again at 8 people per room. When the 25th person joins the line, all 25 people are then split across 4 rooms etc etc. </p>
  
  <p>So far I have come up with this formula:</p>
  
  <pre><code>=IF(IF($A333-8*(COLUMN()-2)&gt;8,12,$A333-8*(COLUMN()-2))&lt;0,0,IF($A333-8* 
  (COLUMN()-2)&gt;8,25,$A333-8*(COLUMN()-2)))
  </code></pre>
  
  <p>however I cant quite get it to work, this will read the LINE column and return or rather place a person in to the room until it reaches the number 8, it then moves to the next room and starts placing people there, but i'm not sure if its possible to set a formula that splits the groups like in my example?</p>
  
  <p>is there a formula that can replicate the above where when the first room is full it splits equally the line into two rooms, with the first room having the higher number first if it is odd eg room A has 4 and room B has 3, then if another person joins the line they are placed in Room B to make it equal/even until both rooms are full at 8 again.</p>
  
  <p>Also there is no limit on amount of people joining the queue or how many rooms opens up.</p>