=SUM Combination In Order To Find Specific Value For Large Amount of Data

Hello all,

Basically I want to derive a specific value through the every possible SUM combination of range of values.

Please find attached a simple example I made.

Thanks in advance.

Looks like a standard "subset sum problem" (https://en.wikipedia.org/wiki/Subset_sum_problem ). Most solutions in Excel that I see use the Solver add-in (http://www.get-digital-help.com/2010...Speed=noscript ). Note that, as an NP-complete problem, as the problem gets larger, it takes a lot longer to compute, with no efficient algorithms to improve the solution. There are approximation algorithms that may be useful, and you may want to research those if your real problem is too large.

Hi,

Why isn't:

=SUM(A4)

a possibility? How many values should be considered?

And if there exists more than one combination of values whose sum is equal to the target value, which should be preferred?

Regards

Hi XOR LX,

Yes, the combination should be of at least two cells (might be more) in order to derive the target value. I don't know how many combinations will give me the targeted value.

Originally Posted by KMVKMVKMV

Hi XOR LX,

Yes, the combination should be of at least two cells (might be more) in order to derive the target value.

And my second question?

Using worksheet formulas only, you'll be restricted to no more than 20 values:

https://excelxor.com/2015/02/10/whic...ple-solutions/

Some VBA or MrShorty's advice would be advisable in such cases.

Regards

I'm sure we can think of other ways. It might help us if we understood what you feel is missing from the existing recommendations.

If the spreadsheet is programmed correctly, Solver should be able to find a solution. It may take a long time on larger variations of this problem, but it should be able to find one.

I could see one programming their own "find every possible combination" algorithm, then use that to test and find which combinations sum up to the desired value. Here's a starting place I came up with for generating permutations without VBA that may serve as a starting point https://www.excelforum.com/tips-and-...ml#post4640511

Here's another discussion for subset sum problems: https://www.excelforum.com/excel-for...ml#post4544017