function to find which cells add up to a given total?

[jbourgui]

Ok, here's the quandry that I'm in... I have a given set of numbers,
and a known total. I want excel to figure out for me which of those
numbers sum to my total. For example:

Code:
--------------------

12
36 1
54
92 1
33 1
57

--------------------

given this set of numbers, I'd like excel to tell me which cells will
add up to 161. if it could put a number, say a 1 in the cell to the
right of each cell being summed, that would be a good indicator
(36+92+33=161). and, by the way, the numbers are disparate enough that
there won't be multiple correct answers; the above set is simplified
for demonstration... Thanks in advance!!

-joe

 

[John Michl]

I did this long ago and used Solver (found in the Tools | Add-ins |
Analysis Tool Pack.) I don't remember the specifics but I probably had
a cell that calculated the total by using sumproduct on the two columns
you displayed. Then in solver set constraints so that the max of the
second column was 1 and the min was zero. I can't remember how I
forced it to give only zero and one in the second column. I remember
that being the key.

Good luck.

- John

 

[Ken Wright]

If you are looking for a solution (Not necessarily the only one) to a subset
of a group of numbers that will add up to a target number, then this can
often be
done with Solver.

Assuming range of numbers in A1:A30, add a set of 0s in B1:B30 and in say
B31 put

=SUMPRODUCT(A1:A30*B1:B30)

Now do Tools / Solver / Set Target Cell 'B31' to 'value of' and put in your
target number. Then, using the range selector under the 'By Changing cells'
section, select cells B1:B30 as the ones to change and hit enter which will
take you back to the first dialog box. Now hit the 'Add' button, and add
the constraint that B1:B30 must be 'bin' (Means binary as in 1 or 0, and
it's one of the dropdowns, so just hit the arrow and select 'bin') and just
hit Solve. You MUST ensure that in this example, when you add the 'bin'
constraint range, you do not inadvertantly include the formula cell B31,
else you will get an error message such as 'Binary Contsraint cell reference
must include only adjustable cells'

Won't do any more than single solution, but for a Finance Dept that will
often suffice in this context.

If you are going to look for more than one target number in the data, then
with that formula in say B31, in B32 type the target number, and in B33 put
=B32-B31.
Now have Solver solve B33 = to 0 with the same constraints. Saves having to
change any values in Solver that way, just type what you want in B32.

Looks quite neat too if you format B1:B30 to a white Font and then use
conditional formatting to highlight values in Col A where Val in Col B = 1

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[jbourgui]

WOW! WHILE I WAS WRITING THIS POST, KEN POSTED THE EXACT SAME ANSWER...
THANKS KEN!!


John, thanks a million! You gave me just enough information to figure
it out for myself... here's the solution for anybody else in the same
bind.

- say you have 10 numbers to compare

- make 2 columns, column A with your numbers, B with 0's. (like in my
orig example)

- make a "named range" of all of the 10 cells in B. call it "foo"

- make a cell (A12) with a formula like this:
=sumproduct(A01:A10,B01:B10)

- open the "Solver" add-in (enable it if it doesn't show up)

- set Target cell to A12

- set "By changing cells:" to "foo"

- click "Value of", and enter your target value

- click Add to add a constraint, put "foo" in the left box and choose
"bin" in the middle box. (click Add & OK)

- click Solve, and watch Excel do all the work for you!



-joe

 

[John Michl]

Ken, thanks for sharing the "bin" constraint. I didn't know about that
one.

- John

 

[Amnon Wilensky]

Hi,

How can I found all the solutions like the example below? The solver shows
only one.



A
B





57
0





62
1





35
1





84
0





20
1





35
1

62
57
35

64
0

35
56
98

98
0

20
20
75

97
0

35
75
208

56
1

56
208


48
0

208



75
0





32
0





47
0












208









Thanks,

Amnon


--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[jbourgui]

amnon, please put the CODE tag around your example to make it
readable... to do this, highlight the original text with spaces or
tabs, and click the *#* button. I'm sure that somebody will be able to
help you out, once we can read your example..

-joe