How to use excel solver to determine optimal order

Hi,

I'm having a large document where I want to adjust the order of a planning to minimize one specific value.

Background example case
Just to explain the matter, I consider that there are 3 different programs in a car washing facility: short, medium and long.

The short program 1 takes 5 minutes, the medium program 2 takes 10 minutes to complete and the long program 3 takes 15 minutes to complete.

The current order first executes all short programs, 1-1-1-1-1-1 for example, then the medium programs and finally all long programs (3-3-3-3-3-3). This means the first programs are finished in 30 minutes and the long programs are finished in 90 minutes. It means that the average mean value of programs per hour will be 12 when you only execute short programs and 4 when there are only long programs left at the end. The goal is to balance these differences by trying to get a constant flow for every hour, let's say six programs per hour and to minimize any fluctuations. This is known as an optimal production mix or scheduling problem

I hope the main problem is understable, but if I translate this to excel the current order is something like this (in minutes):

5
5
5
5
5
5
10
10
10
10
10
10
15
15
15
15
15

The solution to this problem may be easy to generate by hand, for the best planning possible with an average of 6 programs per hour is listed below. This is simply taking a short, then a medium and finally a long program. (1-2-3-1-2-3-1-2-3... instead of 1-1-1-1-1-2-2-2-2-3-3-3-3-3)

5
10
15
5
10
15

But I want to let the excel solver (or a different function if that saves me) to determine this optimal order of cells. So I don't want to change the values of a column, but just want to regroup them in such a way the excel solver can minimize any fluctuations.

The real case contains over 9000 rows each containing 20 specific cells for the equation to define the mean processing time and fluctuations, so I hope I've simplified the matter a bit.

I've also attached the example case in an excel worksheet with the optimal order next to it.

Is this a homework problem?

If not, post a reasonable amount of the actual data the solution would be applied to, including any and all constraints.

No, it's unfortenately not a homework question, would have been cool to ask this to some kind of teacher though.

If you want more data, what would you prefer? There is a very limited acces to the actual data as it contains business secrets, so you might describe it as classified. In essence though, the simplified example case doesn't make so much of a difference with the real case, only that there are 9000 rows for the last 3 months. If you want specific data or a broader data range, I will look into it if I can find a solution for it.

The idea is that the order of the rows is being adjusted by the algorithm which reduces the variation of different programs. If you have a very long program, you don't want to place all long programs next to each other but want to spread it out between the shorter programs to balance the flow per hour. The only limitation therefore is that program numbers are binary and can only be adjusted by reordering, not by changing values.

[edit]
I decided to upload a limited amount of the classified data into a document, with only the relevant data to the problem, with only the first 500 rows. In the first sheet I've stated the current order of programs and the variable processing times for each order. In the second sheet ("current-order+processing_times") I've added some calculations to add the relevant programming time for each order depending on the program and the average processing time for six following orders (six following orders is relevant for the actual case).

So, the objective still is adjusting the order and making the optimal continous flow. You want it to be constant for six following programs with as little fluctuation as possible, and naturaly, also to minimize the total processing time (not relevant for current worksheets and simulations).

(document still uploading)

Still? Is it zipped?

Programs 17 & 18 have zero execution time -- those must be very efficient ...

For background info: programs 17 and 18 are used when something goes wrong.

Then how can they be inserted in abitrary order?

I solved it manually using two methods. Both pick the first 5 jobs (sort of) arbitrarily.

Then one method iteratively chooses the next job among those still to be run that makes the average for the last six closest to the target average.

The other method chooses the next job among those still to be run to make the running total time closest to the pace.

Surprisingly (to me) the first method had a smaller deviation.

I've not attempted to automate either.

I don't know of any standard add-in that would automate this, especially including Solver.

Assuming there is value in the whole enterprise, then there are gains to be had. For your random (?) job order, the deviation in six-job average times is 3.4. For method 1 previously described, it's 0.61, and for method 2, 1.2.

How would you pick the first five jobs?

Method 2 makes it easy; pick any job at random, and then choose additional ones to match the pace, as for all others.

That approach could be used in Method 1 as well, for just just the first 5.

See attached.