+ Reply to Thread
Results 1 to 5 of 5

Minimum number of tables/seats to be used dsitributing agents with 2 shifts

  1. #1
    Registered User
    Join Date
    03-30-2016
    Location
    Barcelona
    MS-Off Ver
    2013
    Posts
    3

    Minimum number of tables/seats to be used dsitributing agents with 2 shifts

    Hi,

    I am trying to use Solver to get the minimum number of tables to be used while you have to distribute certain number of agents with 2 shifts and different starting and ending times into these tables. The desired system (Solver + VBA) should be able to distribute a given number of agents that can vary from test to test. I have made several trials with Solver with an example of 5 agents (file attached) but it seems not to be giving the minimum number and the distribution.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    13,921

    Re: Minimum number of tables/seats to be used dsitributing agents with 2 shifts

    Hello jmrc123 and Welcome to Excel Forum.
    It seems to me that the correct answer should be 3.
    If the "table" in A1:E6 is arranged ascending by start times then the following formula could be applied to see how many agents are occupying tables at any one time:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The number of tables to be used could then be obtained using the MAX function.
    The formula is modeled in J2:J6 while the MAX function populates cell K1.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    03-30-2016
    Location
    Barcelona
    MS-Off Ver
    2013
    Posts
    3

    Re: Minimum number of tables/seats to be used dsitributing agents with 2 shifts

    Hi JeteMc,

    many thanks for the answer. You've reached a possible solution obtaining the minimum number of tables used, but not ordering the agents into these tables which would be necessary if we want to seat them afterwards. I'll think over this approach and try to figure out if there's any way to get this with the data we have now.

    Many thanks.

  4. #4
    Registered User
    Join Date
    03-30-2016
    Location
    Barcelona
    MS-Off Ver
    2013
    Posts
    3

    Re: Minimum number of tables/seats to be used dsitributing agents with 2 shifts

    Hi again. After some work, I've found a way to get the minimum number of tables as JeteMc pointed, the tables with the agents seated & the shift, all prepared for at least 20 agents that can be expanded to more if needed easily.

    Many thanks for your support.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    13,921

    Re: Minimum number of tables/seats to be used dsitributing agents with 2 shifts

    You're Welcome. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help calculating number of agents per 15min interval with data included.....
    By DancingElvis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2016, 10:47 AM
  2. [SOLVED] peRoster Dilemna: calculate hours worked for scific shifts on public holidays using tables
    By Faboolous in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-18-2016, 09:14 AM
  3. Replies: 8
    Last Post: 03-24-2016, 08:13 PM
  4. Replies: 0
    Last Post: 04-11-2013, 10:41 AM
  5. Counting number of agents without sales
    By Saturn in forum Excel General
    Replies: 3
    Last Post: 10-08-2011, 07:02 AM
  6. Find minimum SUM if no minimum number in row
    By zbor in forum Excel General
    Replies: 9
    Last Post: 12-25-2009, 05:04 AM
  7. [SOLVED] Count number of shifts...
    By batman07@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-24-2006, 09:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1