how to get excel to combine numbers to a given total

[karlo]

Thank you for your assistance.

I receive a list of numbers. I need to find
out which amounts will add up to a given total. How would I create a macro
to do this? Is there already an Excel function to do this that I have not
found? It would need to work with currency.

Ex: 9, 12, 7, 4, 15, 8, 10, 3 total needed 18
Solution 8+10; 3+15

 

[macropod]

Answered in microsoft.public.excel.programming

Please don't post the same question separately in multiple newsgroups.

 

[francis]

try using the Solver add-in

Assuming your numbers are in column A . Then, in column B, put zeroes in
every cell next to the values in column A. In C1,put the formula
=SUMPRODUCT($A$1:$A$8,$B1:$B$8), adjust your ranges.

Now in the Solver, in the first dialogue box, enter $C$1 as your Set Target
Cell
and Equal to a Value of 18
In the By Changing Cells box, enter $B$1:$B:$8
click add under Subject to the constraints of:
in Cell reference put : $b$1:$b$8
from dropdown select Bin, click OK and click Solve, Keep solver solution
and look at the table, its should return as :

9 0
12 0
7 0
4 0
15 1
8 0
10 0
3 1


If there is a correct answer, the cells in $b$1:$b$8 will change to 1
instead of 0, and you will have your answer. This usually works, but is
subject to the following constraints, firstly, it is better with a short
lists of numbers, and secondly, if there is more than one solution, it will
only give you the first one it finds



--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[AlBear]

Thank you for your assistance.

I receive a list of numbers. I need to find
out which amounts will add up to a given total. How would I create a macro
to do this? Is there already an Excel function to do this that I have not
found? It would need to work with currency.

Ex: 9, 12, 7, 4, 15, 8, 10, 3 total needed 18
Solution 8+10; 3+15

You could try a $30 excel add-in called SumMatch by evolucion-com-au