Backwards goal seek.

[Chris Berry]

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]

I gather this is not possible

Oh well, worth a shot.

[MrShorty]

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.