View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
Studebaker Studebaker is offline
external usenet poster
 
Posts: 57
Default Which numbers give desired result

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.

"JMB" wrote:

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.


"JMB" wrote:

try this link

http://www.microsoft.com/office/comm...xp=&sloc=en-us


"Studebaker" wrote:

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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Studebaker" wrote in message
...
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