Conversation Re: Optimization Problem with Fixed Discreet Variables in Excel
https://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1204079#M54068
I think it is like this:<BR />Either you write VBA code that tries all possible combinations and picks the best (complicated to do and due to the many possibilities probably very slow), or you accept the second best solution which is where you try to create sets where you try to create pairs of both columns which are both as close to the column average as you can get. This is done by combining the largest with the smallest of A, then the one-but largest and one-but smallest, and etcetera. Depending on the precise numbers another strategy may potentially give a better result, but this strategy will likely work in more cases.Mon, 02 Mar 2020 09:46:43 GMTJan Karel Pieterse2020-03-02T09:46:43ZOptimization Problem with Fixed Discreet Variables
https://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1196268#M53673
<DIV class="paragraphNode wrappable"><SPAN class="textBox"><SPAN class="textWrapper">Hello!</SPAN></SPAN></DIV><DIV class="paragraphNode wrappable"><SPAN class="textBox"><SPAN class="textWrapper">My problem is relatively simple, but I can't find a way to make it.</SPAN></SPAN></DIV><DIV class="paragraphNode wrappable"><SPAN class="textBox"><SPAN class="textWrapper">I have 14 parts of type A and 14 parts of type B and their weights. I need to group they in 7 sets containing 2 of each (each part can be used only once). </SPAN></SPAN></DIV><DIV class="paragraphNode wrappable"><SPAN class="textBox"><SPAN class="textWrapper">I want to group </SPAN></SPAN>the parts so all sets are as close as possible in weight. Would mean targeting the smallest StdDev of the total weight of each set:</DIV><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><DIV class="paragraphNode wrappable"><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Annotation 2020-02-26 161715.jpg" style="width: 400px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/173628i71681028DF1C7E36/image-size/medium?v=1.0&px=400" title="Annotation 2020-02-26 161715.jpg" alt="Annotation 2020-02-26 161715.jpg" /></span></DIV><DIV class="paragraphNode wrappable"> </DIV><DIV class="paragraphNode wrappable"><SPAN class="textBox"><SPAN class="textWrapper">Anyone can help?</SPAN></SPAN></DIV>Wed, 26 Feb 2020 15:20:41 GMThttps://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1196268#M53673Telles652020-02-26T15:20:41ZRe: Optimization Problem with Fixed Discreet Variables
https://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1196449#M53681
<P><LI-USER uid="568684"></LI-USER> See attached. Next time, please upload an Excel file rather than a screen-shot.</P>Wed, 26 Feb 2020 16:11:23 GMThttps://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1196449#M53681Jan Karel Pieterse2020-02-26T16:11:23ZRe: Optimization Problem with Fixed Discreet Variables
https://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1196486#M53684
<P><LI-USER uid="22322"></LI-USER> </P><P> </P><P>Thank you for the attempt!</P><P>On the file you've sent the sets have the biggest possible StdDev of the totals. </P><P>I would like to be able to combine to find the smallest possible StdDev.</P><P>I've also tried to use the Solver, did not work because I need it to iterate using fixed specific values of the table.</P><P>The file is attached this time!</P><P>Regards,</P><P> </P><P> </P>Wed, 26 Feb 2020 16:23:16 GMThttps://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1196486#M53684Telles652020-02-26T16:23:16ZRe: Optimization Problem with Fixed Discreet Variables
https://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1196562#M53690
Is set A always smaller than set B? If so your second table is likely to be the best solution.Wed, 26 Feb 2020 16:49:50 GMThttps://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1196562#M53690Jan Karel Pieterse2020-02-26T16:49:50ZRe: Optimization Problem with Fixed Discreet Variables
https://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1203182#M54027
<P><LI-USER uid="22322"></LI-USER> No, not always.</P><P>I'm still stuck with this...</P>Sun, 01 Mar 2020 15:06:01 GMThttps://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1203182#M54027Telles652020-03-01T15:06:01ZRe: Optimization Problem with Fixed Discreet Variables
https://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1204079#M54068
I think it is like this:<BR />Either you write VBA code that tries all possible combinations and picks the best (complicated to do and due to the many possibilities probably very slow), or you accept the second best solution which is where you try to create sets where you try to create pairs of both columns which are both as close to the column average as you can get. This is done by combining the largest with the smallest of A, then the one-but largest and one-but smallest, and etcetera. Depending on the precise numbers another strategy may potentially give a better result, but this strategy will likely work in more cases.Mon, 02 Mar 2020 09:46:43 GMThttps://techcommunity.microsoft.com/t5/excel/optimization-problem-with-fixed-discreet-variables/m-p/1204079#M54068Jan Karel Pieterse2020-03-02T09:46:43Z