| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Which numbers give desired result
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
(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 "Studebaker" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
Thank you , Bernard. I appreciate your help.
"Bernard Liengme" wrote: (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 "Studebaker" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
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. "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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
Thank you, JMB. I'm going to go to the website and try it out.
I appreciate your help. "JMB" wrote: 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. "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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
Hello,
Maybe this thread can help, too: http://groups.google.co.uk/group/mic...891ee261b1af36 Regards, Bernd |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
Thank you, it looks like it might help!!
"Bernd P" wrote: Hello, Maybe this thread can help, too: http://groups.google.co.uk/group/mic...891ee261b1af36 Regards, Bernd |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
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" wrote: Hello, Maybe this thread can help, too: http://groups.google.co.uk/group/mic...891ee261b1af36 Regards, Bernd |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
Hello,
Send me your file then. I can have a look... Regards, Bernd |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Which numbers give desired result
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 "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup does not give the result in the cell even though it is | Excel Worksheet Functions | |||
| Trying to find Matches and Give a Result | Excel Worksheet Functions | |||
| which function can get the desired result? | Excel Worksheet Functions | |||
| Provide a match from 2 cells to give a result from another | Excel Worksheet Functions | |||
| HOW TO GET A DESIRED 'SUMIF' RESULT FROM A FILTERED DATA | Excel Worksheet Functions | |||
Linear Mode
