Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Goal seek output | Excel Discussion (Misc queries) | |||
goal seek circular reference | Excel Discussion (Misc queries) | |||
Null values in charts and how to override the goal seek functionality | Charts and Charting in Excel | |||
Goal Seek | Excel Worksheet Functions | |||
goal seek wont calculate an accurate value past 3 decimal places | Excel Discussion (Misc queries) |