Excel, Find Number Combinations that Total a Sum

[dlh6213]

I’m trying to find a way, either with Excel or another program, to find out what combination of numbers will equal a predetermined sum. I’ve tried Excel Help and the internet, and it appears possible, but all the examples are far more complicated than what I’m trying to do and they don’t seem to work for me. Here’s an example of what I’m trying to do:
If I have the following set of numbers:
1.00
1.50
2.00
2.30
2.90
5.30
5.40
6.30
6.90
7.40
7.80
8.30
9.50

I’d like to find out which combinations will total 9.6 and 9.7; the same number can be used multiple time (i.e. 1.00 eight times plus 1.5 = 9.5)

It seems like ?Solver in Excel should work for this, but I can’t get the results I want.

[JamesCherrill]

I doubt that you will find an off-the-shelf solution, nor will you find an easy one.
You need to traverse a tree with nodes for each value x1, x2, x3 etc.
Looks like a recursive algorithm.
How much do you really want a solution?

[JamesCherrill]

If you're still interested...
Here's a little runnable demo I hacked up in Java to show how the recursive solution works:

    int[] values = {1, 2, 9, 3};
    int target = 8;

    tryValue(0, 0);

    // recursive algorithm to try all possible combinations of values and
    // multipliers that add up to the target.

    // LIFO history of steps leading to the current one...
    Deque<String> solution = new LinkedList<>();

    void tryValue(int valueIndex, int runningTotal) {

        if (valueIndex >= values.length) {
            return; // tried every value
        }

        int value = values[valueIndex]; // just for convenience
        int multiplier = 0;
        int newTotal;

        // keep trying multiples of this value until it exceeds taget...
        while ((newTotal = runningTotal + value * multiplier) <= target) {
            solution.push(multiplier + "x" + value);
            if (newTotal == target) {
                System.out.println(solution + " = " + target);
            } else {
                // try adding (multiples of) the next value....
                tryValue(valueIndex + 1, newTotal);
            }
            solution.pop();
            multiplier++;
        }

    }

[dlh6213]

Thanks for the response! I was really hoping there would be an easy way to do this - preferably with Excel, but I guess not. Thanks for the suggestion; maybe I'll give it a go.

[JamesCherrill]

I guess you could use Visual Basic for Applications (VBA) in Excel - my Java code is simple enough that you should be able to translate into vba without too much difficulty.