LinkBack URL
About LinkBacks
The attached file shows two sets of column numbers. Cell numbers in each row can be shuffled between columns A, B & C, and F & G, such that the sum of each set of individual columns is a minimum.
Cheers.
The attached file shows two sets of column numbers. Cell numbers in each row can be shuffled between columns A, B & C, and F & G, such that the sum of each set of individual columns is a minimum.
Cheers.
Can you give example of the final result you want.
- Battle without fear gives no glory - Just try
Attached Example 2.
By swapping (say) numbers 9 and 26 in col K & L the totals are probably close to the minimum values. Is it possible to get the totals to zero? That exercise would be very time consuming.
Similarly for the other 2 col table, swapped numbers are shown in col P & Q. This table shows little improvement but the difference in totals has narrowed from -17 to -3.
Hope this helps.
You need to install an add-in called Open Solver.
Www.opensolver.org
Then set up the model as attached.
I set up the model for you for 2-column scenario and 3-column scenario.
This is a Mixed Integer Programming problem.
Best,
Jimmy
Last edited by astupig; 08-13-2020 at 04:18 AM.
I had given up!
Excellent solution and solves my problem. Thank-you.
You are welcome. Glad it helps.
Jimmy
Nice solution Jimmy! Got any recommendation for learning about "Mixed Integer Programming"?
Quick question do one really need all the constraint you used in your "2-column" setup?
I made a modification of your "2-column" setup and one can either set the object function to "=Abs(G30)-Abs(H30)" or "=Abs(H30)-Abs(G30)". This will change the values in the
F column but the value of the object function is 1 in both cases.
Alf
Hi Alf,
See below link for the book of Decision Making, there are many cases regarding Mixed Integer Programming.
https://opensolver.org/opensolverwor...onModeling.pdf
=Abs()-Abs() is an excellent idea for considering the different of two sums.
Very nice! I never thought of this kind of "ABS" function is accepted by Open Solver.
But one issue, the abs-abs will lead to unexpected result: for example, 59 and -59.
See attached, I modified B4 from 4 to 104 and run your model again and you can see the result is not what we expect.
The variables U,V I introduced is from the idea of the book recommended by Open Solver. See Section 6.1 Absolute Values
https://opensolver.org/opensolverwor...mingTricks.pdf
Jimmy
Last edited by astupig; 08-14-2020 at 08:07 PM.
Hi Jimmy
Thanks for the links and feedback especially about the problem using the Abs() function. As the Mixed Integer Programming is a technique I've never known before I need to study the how and the why of it so I can use it properly.
Alf
Ps Not using the Abs() function and just taking in sum difference i.e. "=G30-H30" or "=H30-G30" works right on your last uploaded file as well as the previous ones. If you got time could you check and see if you could find a combination of number where it goes wrong?
Last edited by Alf; 08-15-2020 at 12:56 AM.
Hi Alf,
You are correct. The 2-column case =G30-H30 works properly. So no need extra variables U,V.
Thanks for pointing this out.
Jimmy
We can also minimize directly one of the two sums, for instance G30, and only add the constraint G30 >= H30. This works also for the 3 column problem.Originally Posted by Alf
By the way, Solver complains that the problem is not linear, since there is an IF function in columns G:H. OpenSolver performs only a quick linearity check and may report erroneous results. As a workaround, we can write
=(1-F3)*B3+F3*C3
in cell G3.
HTH,
Francesco
Aim high or don't even try.
---------------------------------
If your question has been answered, don't forget to mark the thread as SOLVED.
If you find an answer helpful, click on the star icon at the bottom of the post.
Hi Francesco,
Thanks. Yes, the if function may appear non-linear. Your =(1-F3)*B3+F3*C3 is better.
Jimmy
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks