## Sunday, August 10, 2014

### yogi_Find Mutually Exclusive Combination Of Values In Cells A3 to A11 and B3 to B11 That Yields Minimum Average Of Values In C3 to C11

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-10-2014
post by Chris Schwarz:
Optimization Problem: Mutually Exclusive Inputs and a Minimized Output
First, a little background:

Given two categories of inputs, call them A's and B's, a result, call it "C", is computed for all permutations of A and B.

For example, we have A1, A2, B1, and B2.  This results in A1-B1, A1-B2, A2-B1 and A2-B2.  Each combination may be considered a cartesian coordinate system (XXX|YYY - XXX|YYY), and the distance between them is the value of C.

What I would like to do is have an automatic minimization for the summed distance such that all A's and B's are used once and only once.

I hope this is clear enough of a description, but if not, I'll provide more details on the question below.

Given the following A's:
 379|467 387|474 378|471

And the Following B's
 470|505 472|505 447|506

We have the following permutations, with calculated value C
 387|474 447|506 68 378|471 447|506 77.4 379|467 447|506 78.4 387|474 470|505 88.6 387|474 472|505 90.5 378|471 470|505 98.1 379|467 470|505 98.6 378|471 472|505 100 379|467 472|505 100.5

What I would like is for a function to take these results and give those combinations of A and B that produce the minimized value of all summed C's such that each A is only used once and each B is only used once.

This is necessary due to the ever increasing number of permutations with an increasing input - it makes doing it manually too time consuming and prone to error if done quickly.  For example, 30 A's and 30 B's yields 900 combinations whereas 31 A's and 31 B's yields 961 combinations.