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

                          Google Spreadsheet   Post  #1723
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-10-2014
post by Chris Schwarz:
(https://productforums.google.com/forum/#!mydiscussions/docs/BL06Yxlu5mg)
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|474447|50668.0
378|471447|50677.4
379|467447|50678.4
387|474470|50588.6
387|474472|50590.5
378|471470|50598.1
379|467470|50598.6
378|471472|505100.0
379|467472|505100.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.

Thank you in advane.
-----------------------------------------------------------------------------------------------------------------------------------------------