Backwards goal seek.
 

Is there any way to have excel look at a list of numbers and return
possible combination of cells that would equal a specific value? For
instance:

List Sum 60
45 Possibles A2+A3, A4+A5+A6
45
30
30
10

Where List and Sum are givens and Possibles would give various options
for arriving at the sum.

If this is not possible, is there a way to do this when there is only
one possible formula?


--
Chris Berry
------------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165
View this thread: http://www.excelforum.com/showthread...hreadid=560458

Backwards goal seek.
 

I gather this is not possible :(

Oh well, worth a shot.


--
Chris Berry
------------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165
View this thread: http://www.excelforum.com/showthread...hreadid=560458

Backwards goal seek.
 

It's possible. I couldn't quite grasp your sample numbers, but here's a
simple example.

Column 1: list the digits from 1 to 9. Basically we will be asking,
"what combination(s) of these digits sum up to a number (say 25).
Column 2: a series of 1's and 0's
Column 3: =A1*A2
At the bottom of column 3: =sum(C1:C9)

I don't think goal seek has enough flexibility for this operation, but,
using Solver:
Set target cell:=C11
To value of 25
By changing B1:B9
Subject to constraint B1:B9=binary

This will find a combination that adds up to 25. Where there are
multiple possibilities, you have to change the starting 1's and 0's in
column 2 and adding othere constraints to the solver model.

Knowing when you've found all possible combinations will be quite
tricky, because there may be cases where there are a lot of possible
combinations. On the other hand, when Solver comes back with, "couldn't
find a solution" is it because there really is no solution to be found
or because of bad initial conditions.

I must admit, that I don't do this kind of thing with my spreadsheets,
so that's about as much as I know. Hope it helps.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=560458