Which numbers give desired result

[Studebaker]

Hello,

I have a list of maybe 20 or so $ amounts on an invoice that I need to
figure out which combination of these #s add to a total of $151.60.

I thought goal seek or scenarios would work but they don't do exactly what I
need. The internet doesn't have an analysis add-in pack that fits the bill
either. This is for Excel 2003.
Does anyone know how I can do this--macro, function w/ vlookup...I don't know?

Thank you very much for your help.

Studebaker

 

[Bernard Liengme]

Assume $ values are in A1:A20
In B1:B20 enter 1 in each cell
In C1 enter =A1*B1, copy down to C20
In C21=SUM(C1:C20)
Tools | Solver..... Target Cell C21; Value is 151.60; Changing Cells B1:B20;
Add Constraint that B1:B20 are BINARY
Hit Solve
This should work if there is an answer. Be Aware that there could be more
than one correct answer.
best wishes

 

[Studebaker]

Thank you very much, Bernard! It worked.

a) There might be a danger that there are more than one correct answers. Is
there something I can do so that I can have a list of all possible scenarios?

b) Also, on one of the invoices it returned possible #s in column B and C as
exponential #s--i.e. 3.0009 X 10 to the -9. Did that mean that there were no
$ amounts I entered that could be used to equal my total?

Thank you very much for your help!

 

[Bernard Liengme]

(b) Excel often gives very small values when zero is expected

 

[JMB]

try this link

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

 

[JMB]

one minor issue w/the findsums macro referenced in the link is if the first n
items add up to your target, it misses that particular combination.

 

[Studebaker]

Thanks, JMB.
I haven't tried it yet but do you mean that once the macro finds the
combination that adds to the sum I'm looking for it stops looking for other
combinations? I got lost when you said "...it misses that particular
combination".

Thanks.

 

[Studebaker]

Thank you , Bernard. I appreciate your help.

(b) Excel often gives very small values when zero is expected

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[JMB]

if you have

1
2
3
4
5
6

and want all combinations that add up to 6, it misses the combination 3+2+1
(the first n consecutive numbers if they happen to add to the target amount).
I don't know if this has subsequently been corrected and have not yet taken
the time to analyze it and see if I can correct it. And I *think* it stops
after filling an entire column, so if there are more than 65536 combinations
(pre XL2007) it might not find every single combination. But if there are
more combinations than that, I would look for a faster solution than XL.
Otherwise, it finds all combinations based on what I've seen and read.

you could google this site for "findsums" to identify threads where the
macro was offered as a solution and see what other suggestions or comments
were made to OP's with similar issues.

 

[Studebaker]

Thank you, JMB. I'm going to go to the website and try it out.

I appreciate your help.

 

[Bernd P]

Hello,

Maybe this thread can help, too:
http://groups.google.co.uk/group/mi...&lnk=gst&q=michael+schwimmer#97891ee261b1af36

Regards,
Bernd

 

[Studebaker]

Thank you, it looks like it might help!!

 

[Studebaker]

Bernd P,

I followed your link below and had a closer look at the links on that
page--http://michael-schwimmer.de/vba096.htm
and http://www.mrexcel.com/pc09.shtml.
a) Michael Schwimer's code is in German and I found when I tried to run it
it didn't work. I don't think this will work for me since this is in German,
is that right?

b) I copied the sample list of numbers into column A of a blank spreadsheet
along with the code from Mr Excel's page but when I tried to run the macro
called "challenge" I got 0s (zeros) in cells E10, F3, F4, F7 & F18 and the
current time (Ex: "11:46:12 AM") in cells F14 & F15 and that's it. The other
macros "COPY_SOLUTIONS", "RESUME_Challenge", and "RESUME_LAST_SOLUTION"
didn't do anything.
Can you let me know if I did something wrong? I'm a beginner w/ VB and I
can't even begin to understand the code.

Thanks very much.

Studebaker

 

[Bernd P]

Hello,

Send me your file then.

I can have a look...

Regards,
Bernd

 

[Dan O''Connell]

Bernard -

I've tried this and I always get the message "Too many adjustable cells"
when I click on the Solver button. My data set is 519 records. Any ideas?

Dan