Minimum number of machines problem ...

[elleirdad]

I am trying to solve a seemingly common business problem, but I am not sure how to use Excel to do it. My problem is like the "sheet metal" problem or the "product mix" problem. So, here it goes:

1. I want to know the fewest number of machines that I need to buy to service all of my customers.

2. Each machine can handle up to X number of users. (Lets say that each machine can handle 500 users.)

3. I have N customers. (Lets say 20 customers) Each customer can have between 25 and 500 users. So, I could have Customer 1 with 50 users, customer 2 with 250 users, etc. (I can have a fixed number of customers, but I need to be able to adjust the number of users for each customer.)

4. All of a given customer's users must be on the same machine. For example, if my machines can handle 500 users then, I can have customer 2 with 250 users and customer 3 with 250 users. However, if customer 4 has 300 usersm I cannot put customer 2's users and some of customer's 3 users on one machine.

5. In an ideal universe, I would like to have array of numbers that look like the following.

Number of customers with 50 users: 1
Number of customers with 75 users: 2
Number of customers with 100 users: 0
Number of customers with 150 users: 4
Number of customers with 200 users: 2
Number of customers with 250 users: 1
(and so on, up to 500 users)

The result of the calculation is the minimum number of machines needed to handle the users without splitting a customer across more than one machine.

What would this take to do? I do have Excel 2007.

 

[wsjackman]

you should probably look at the number of non used sections of the machine rather than the number of used sections

let N(xxx) be the number N of customers with xxx number of users

first you need at least one machine for each customer with more than 250 users.

=countif(range,">250")

you also need at least one machine for every two customers with 250 users. if you have an odd number you need one more.

=round(N(250)/2,0)

you now have a series of machines with from 0 to 250 unused sections

it now gets more complicated

if you only have 20 customers, I would probably do the rest by hand

start out with the machine with the largest number of unused sections and fill it as full as possible using the customers with the largest number of users from the remaining unused customers.


go to the second largest number of unused sections and continue filling.

If you finish all the unused customers before you run out of machines, you have the minimun number of machines needed.

if you have some customers still not covered, it can be more complicated. you might be able to rearrange the existing groupings to be able to insert one or more customers, but if the total users for the unused customers is greater than the number of unused sections remaining, and is less than 500, I wouldn't try to optimize any further. just add another machine for the excess.

Numerically, getting the absolute minimum number of machines for any given set of combinations is possible, but the algorythms for doing so, gets very complex. and the best algorythm depends on the details of the combinations. I would recommend that you try something similar to wnat has been described above and try to define what errors you find doing them so that a specific algorythm could be recommended.

you can go into further

 

[elleirdad]

Thank you ...

I agree that it would be easier to do the rest by hand. But, I want to create several different scenarios for different customer sizes. Even though the 20 customers is finite for the model, the distribution will vary.

Therefore, I think I need to come up with a model.

 

[wsjackman]

if the distributions are in units of 25, there are 20 possible number of users (excluding 0) for each customer. there are, therefore, 20 raised to 20th power number of possible combinations keeping customers unique. (>10^26)While the number of combinations for non unique customers is much smaller, it is still large. ( I do not know how to calculate it)

the simplest solution would be to accept the smallest number resulting from a particular methodology. for example a single gothrough based on the structure discussed in my first submittal. without trying to improve by trading back and forth, after the first allocation there will be distributions for which, this will not give the "Fewest" machines needed.

I feel a macro is needed if you do not want to have any by hand calculations. I am not good with macros but will try if no-one else gives a suggestion in the next few days.

bump the thread if there is no sugestions in the next couple of days.