Thread: How solver can take the Max value every time

assuming your data values are in cells b2:b4, this will show the rak order of each line in that row

=CHOOSE(RANK(B2,$B2:$D2),"Line 1","line 2","Line 3")

for example, copying this formula to three adjacent cells will return "Line 1 " in the first (most productive) "Line 3" in the second cell (third most productive) and "Line 2" in the third cell (thirs most productive) if the values in b2:b4 are 25, 20 and 23

pl post a file with dummy data containing before and after information.

Hi

Perhaps a binary "gate" may be of help? I'll upload an example so you can see for yourself if this could be of use to you.

I'll also have a closer look at your model so I can understand your problem properly.

Alf

Dears,

thanks for the support. I'm very thankful for your care and welling to help and support without return. GBY.

Hi nilpman

Did you have a look at the workbook I uploaded? Is this of use to you?

Alf

Thanks again,

the whole idea is to utilize the lines for maximum production and to meet the yearly production plan, so each line has different capacity and cannot handel the whole production volume, so we have to use the second line when we reach to the max capacity of the first line.

example, from the above example file, grade x1 will be produced on R1, and the remaining quantity for that period is to be produced on line R2, if it is free,. Please note that line R3 can not produce x1.

finally, at te end of the production period I have to fulfil the prodction requirement of all the three product by utilizing the lines in good way.

Regards,

Nlpman

Hi Alf,

thankls for your follow up, I have posted more information that could help you to understand the issue better.

Nlpman

Thanks Alf

I think you did understand the problem now, so first let us focus on the overall yearly productio , as you said the solver selected on line and start the production and the remaining quantity was produce in the other line, BUT the solver did assume that all the lines are having the same production rate which is not, and this is the main problem, i need it to look for the higher production rate and start the production first on that line abd the remaining on the second highest line .
So how can i ask solver or excel to do that and let us focus on the yearly production only.

Thanks again.

Nlpman

BUT the solver did assume that all the lines are having the same production rate which is not, and this is the main problem, i need it to look for the higher production rate and start the production first on that line abd the remaining on the second highest line .

You could try to add a cost factor i.e. let the line with the highest production rate have the lowest cost factor. But there is no concept of time in Solver. All things happen at the same time. So if "line capacity" is the limiting factor does it matter which line gets used first as long as all are used to a 100 %?

And if production volume is the limiting factor then if you have a cost factor as well Solver will utilize the "cheapest" line to a 100 % and take the balance from line 2 (second cheapest) or use line 1 and 2 to a 100 % taking the balance from line 3.

Alf

I did not get it, and here is more info.

The issue is that, the solver did assume all the lines are having the same production rate. This is wrong assumption, if you look to the second table you will see that each line has better performance for one grade and lower for the other one. So i would like solver to start producing the grade X1 for example at line1 ( R1) because it has the highest production rate per hour for that grade , then the remaining quantity to be produced on the second line and so on.

Regards,