Need Help Running All Combinations of a Set Number of Values through 4 equations

  • I have 4 different equations in 4 separate cells. I am trying to test all possible value combinations from a set number to see which combination results in the smallest sum of results.


    This needs to be done with combinations not permutations as order does not matter


    For example...


    Say I have 100 available and 4 equations as follows:


    Equation 1) 2x + 5 Placed in cell A1
    Equation 2) 9x^3-3x+2 Placed in cell B1
    Equation 3) 3x^2-8 Placed in cell C1
    Equation 4) x^4+2x Placed in cell D1


    I want to run Equation 1 with x=100, Equation 2 with x=0, Equation 3 with x=0 and Equation 4 with x=0
    Next, Equation 1 with x=99, Equation 2 with x=1, Equation 3 with x=0 and Equation 4 with x=0
    Continuing with all possible combinations until I have Equation 1 with x=0, Equation 2 with x=0, Equation 3 with x=0 and Equation 4 with x=100.


    This example has 176,851 combinations


    I need to compare the sum of the results of the four equations for all iterations, and determine which iteration resulted in the smallest sum.


    Is this even possible? Thank you for any help provided.

  • Thank you for your reply StephenR,


    If we reduced the question to say, 4 into 3 equations, the results would be as follows:


    {4,0,0} -1
    {3,1,0} -2
    {3,0,1} -3
    {2,2,0} -4
    {2,1,1} -5
    {2,0,2} -6
    {1,3,0} -7
    {1,2,1} -8
    {1,1,2} -9
    {1,0,3} -10
    {0,4,0} -11
    {0,3,1} -12
    {0,2,2} -13
    {0,1,3} -14
    {0,0,4} -15


    identical items into distinct bins... n+r-1/r-1... 4+3-1/3-1... Gives 6 choose 2


    6!/2!(6-2)! = 6!/2!4! = 720/48 = 15


    I believe that should be the same logic for 100 identical items into 4 distinct bins, which results in the 176,851 combinations



    shg from Excelforum.com came up with this, which solves the combinations perfectly, and verifies the 176,851.




    This solves the question I had, but I am trying to find a way to display the results for 100 with 3 bins and 100 with 2 bins as well, and am not sure how to modify this code to achieve that. 3 individual functions would be fine.


    For 100 identical items into 3 distinct bins, it would be 102 choose 2, or 5151 combinations.
    For 100 identical items into 2 distinct bins it would be 101 choose 1, or simply 101 combinations.


    I would like to be able to display those as well. I imagine it is a modification of shg's code above, but I am not sure what to modify.


    I would appreciate any advice you might provide.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!