If I have a list of numbers (16), and I have a target number, is there a way to find the combination of the list of 16 that will provide the target number? 16Factorial is huge, I couldn’t find anything online… thanks.
You’ll have to be a lot more specific in what you’re trying to do.
Are you multiplying, dividing, adding, or subtracting the numbers? Or something else altogether? A bit more info would help.
Goal Seek does it, but you can only have it alter one cell. If the other 15 cells can be related to that one cell (or each other) by formulas, goal seek should do it for you. If your target number is a maximum or minimum, Solver might do the trick. Here, you can vary more than one cell, but if you have too many, you might have a local extreme, rather than a global one.
Goal Seek does it, but you can only have it alter one cell. If the other 15 cells can be related to that one cell (or each other) by formulas, goal seek should do it for you. If your target number is a maximum or minimum, Solver might do the trick. Here, you can vary more than one cell, but if you have too many, you might have a local extreme, rather than a global one.
I have a list of charge-offs from varying states in a loan portfolio. I have a regional # that is comprised of certain states, meaning some combo of states charge-offs will sum to a region’s number. I am just uncertain which states make up that number without access to my point of contact tonight. Not urgent, but didn’t know if any of you wizzes knew how to do that… I don’t think goal seek is an option… Thanks for your help.
well the only way that the the sum can be unique is for the numbers to be of the for b^x for integer x. Then it’s just a matter of writing the target number in base b which is simple.
Do you know how many states are part of the sum? Or are you trying to find that as well?
This is actually much more difficult than you would think. If you post your email I will send you a spreadsheet that will do it.
No way. I don’t even know what the problem is exactly but its easy…
He needs to check over 65k combinations to see see which ones total a specific number if my understanding is correct. Automating this task is tricky when you start digging into it. http://www.mrexcel.com/pc09.shtml
Is this just the knapsack problem? (http://en.wikipedia.org/wiki/Knapsack_problem) If so there’s no polynomial-time solution.
You could get some help with a table, but I think that 16 factorial is too large. Something like: Have your 16 numbers and true / false next to each Have a SUMIF at the bottom, along with a test whether it equals your answer. Use the test as the result for the table. The input for the table will then need to drive which value the trues and falses take. So, in the 3 item case, your column could go 100,110,111,010,011,001 with for example the first true / false being true if the first number if true etc. Writing a forumla to generate all the possibilities shouldn’t be too hard, but doesn’t come to mind! Use all 1 million rows of Excel 2007 or however many it is, F9 and wait.
Yes, this is a variation of the knapsack problem. Since all of the numbers in the set are less than the combined total he is trying to find, there’s no algorithmic solution. Every total must be checked using a recursive procedure.
TheBigBean Wrote: ------------------------------------------------------- > You could get some help with a table, but I think > that 16 factorial is too large. > > Something like: > Have your 16 numbers and true / false next to > each > Have a SUMIF at the bottom, along with a test > whether it equals your answer. > Use the test as the result for the table. > The input for the table will then need to drive > which value the trues and falses take. So, in the > 3 item case, your column could go > 100,110,111,010,011,001 with for example the first > true / false being true if the first number if > true etc. Writing a forumla to generate all the > possibilities shouldn’t be too hard, but doesn’t > come to mind! > Use all 1 million rows of Excel 2007 or however > many it is, F9 and wait. A table is two dimensional. This problem has sixteen potential dimensions. I agree with your theory but I doubt there is a practical application.
i have a solution: in columns A1-A16 type in state charge-offs in columns B1-16 type in 0’s in D1 = sumproduct (A1:A16,B1:B16) in E1 = your regional number in D3 = D1-E1 now use solver so D3 = 0 and add constraints so that B1-B16 are binary and by changing B1-B16. it will take some time to solve but should give answer in the end, as long as excel doesnt blow up.
Email sent.
I’ll respod when I leave the office, no access to web-based email here. Thanks for the help.