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:

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.0 |

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.0 |

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.

Thank you in advane.

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.

*-----------------------------------------------------------------------------------------------------------------------------------------------*