+ Reply to Thread
Results 1 to 9 of 9

Problem nobody can solve!!!!

  1. #1
    Registered User
    Join Date
    08-20-2007
    Posts
    3

    Cool Problem nobody can solve!!!!

    I have a problem. I have a product recipe with a known total percentage mix. Within this mix are 50 known ingredients all with certain percentages attached. I need to know how many different ways only 8 of the percentages can come back to the known total. Perplexing or what. I think Data table qry can do it but I am not sure. Can anyone help me.

  2. #2
    Forum Contributor
    Join Date
    12-12-2005
    Posts
    667
    I would use the Solver function, but with 8 variables that might be difficult!
    Best regards,

    Ray

  3. #3
    Forum Contributor
    Join Date
    12-12-2005
    Posts
    667
    You could do this by iterating the different variables between their constraints values using a binary search.

  4. #4
    Registered User
    Join Date
    08-20-2007
    Posts
    3

    Thanks Ray

    Thanks but even though I have been using spreadsheets since the 70s I have never ventured into Solver and woudnt know where to start.

    Regards

    geoff

  5. #5
    Registered User
    Join Date
    08-20-2007
    Posts
    3

    Unhappy Problem!!!

    Said it was impossible

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007
    There are

    50! / (50-8)!
    = 21646947168000 combinations

    though many of these will not come back to your known total.
    Even so, that's a bit daunting to begin with.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  7. #7
    Forum Contributor
    Join Date
    10-27-2006
    Location
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Posts
    517

    Can you be Clearer?

    I don't think I even understand what the problem you are trying to solve is!

    You have 50 ingredients.
    Will all of these can be present in the final mix?

    42 of these ingredients have known fixed amounts in the final mix,
    8 of the ingredients can have varying amounts in the final mix,
    the Total Amount in the final mix must be constant.

    Is the above correct?

    Looking at the above there is obviously no single solution, unless there are other constraints. So what are you trying to find? perhaps the maximum and minimum values that the 8 variable quantities can take?

    In my opinion the problem is insoluble because it is not posed correctly.

  8. #8
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Mark the problem is easy conceptually. He has a value attached to each of 50 items, how many ways can he get the total of eight of these values to equal a given target. The problem is that there are many different combinations that could be a solution as illustrated earlier in the thread.

    eg if you had 5 items with a value

    a 1
    b 2
    c 3
    d 2
    e 1

    how many times can you pick 2 items whose total value is 3?
    a & b
    a & d
    b & e

    but the possible combinations are way way larger with 8 from 50

    Is that any clearer?

    It could be done with some iterative loops but would take a very long time!

    Regards

    Dav

  9. #9
    Forum Contributor
    Join Date
    10-27-2006
    Location
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Posts
    517

    Not an Excel Problem

    Ta, I see it now.
    I wouldn't try to solve this with EXCEL, though I suppose you could do it with Visual Basic.

    The simplest way would be a plain exhaustive search through every possible combination. Time consuming but not ridiculous on a modern PC.

    It would probably be possible to reduce the search by sorting the percentages first, which could then allow scenarios that will not work to be discarded quickly.

    It is solvable, but not in any nice neat manner.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1