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.
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:
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.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
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.
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks