Problem nobody can solve!!!!

**geoffcol** · 08-20-2007, 02:59 PM

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.

**raypayette** · 08-20-2007, 03:04 PM

I would use the Solver function, but with 8 variables that might be difficult!

**raypayette** · 08-20-2007, 03:08 PM

You could do this by iterating the different variables between their constraints values using a binary search.

**geoffcol** · 08-20-2007, 04:01 PM

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

**geoffcol** · 08-20-2007, 05:21 PM

Said it was impossible

**Special-K** · 08-20-2007, 05:33 PM

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.

**Mark@Work** · 08-21-2007, 11:19 AM

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.

**Dav** · 08-22-2007, 05:54 AM

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

**Mark@Work** · 08-22-2007, 12:53 PM

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.