Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly add numbers in a group
Hello,
I have a list of 11 numbers. I need to use some of those numbers to come to a total of $3,144.28. I don't need all of those numbers, but I don't know which ones to exclude. I also don't know how many of the 11 numbers I need. Is there a function or something that would add random numbers in the group until it finds what numbers make up the total I need? Thanks, Michelle |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly add numbers in a group
Try this Tom Ogilvy classic for a way using Solver:
http://tinyurl.com/5kx9bw -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Michelle" wrote: I have a list of 11 numbers. I need to use some of those numbers to come to a total of $3,144.28. I don't need all of those numbers, but I don't know which ones to exclude. I also don't know how many of the 11 numbers I need. Is there a function or something that would add random numbers in the group until it finds what numbers make up the total I need? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly add numbers in a group
"Max" wrote:
Try this Tom Ogilvy classic for a way using Solver: http://tinyurl.com/5kx9bw Nice! If Solver cannot find an exact sum (certainly try that first), I would solve for the Min of ABS(D1-1391.03) in Tom's example. But either way, for N numbers, Solver might have to look at 2^N - 1 combinations. In Excel 2003, Solver's Iteration option is limited to 32767. Does that mean this approach might not be suitable when N is more than 15? (Unless you get lucky, of course.) Or am I misunderstanding the Iteration option? ----- original message ----- "Max" wrote in message ... Try this Tom Ogilvy classic for a way using Solver: http://tinyurl.com/5kx9bw -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Michelle" wrote: I have a list of 11 numbers. I need to use some of those numbers to come to a total of $3,144.28. I don't need all of those numbers, but I don't know which ones to exclude. I also don't know how many of the 11 numbers I need. Is there a function or something that would add random numbers in the group until it finds what numbers make up the total I need? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly add numbers in a group
In Excel 2003, Solver's Iteration option is limited to 32767.
Does that mean this approach might not be suitable when N is more than 15? Hi. That value is an internal value that triggers a program interruption, and not the final end of the program. If this limit is hit, one would be Prompted to continue or end the program. To hit a limit of 32767, one would have to set the Time limit to a very high number as well, in order for the loop limit to fire before the Time limit fires. What this Loop value really represents is a little hard to tell. For example, I've seen models run for a short while, even though the loop was set at 1. In vba, one can write code to intercept these program interruptions, make decisions based on why the code was called (either Time or Iteration), and continue on. With Iteration set at 1, I've had to have a large Integer model running for a while before the code was called based on hitting the Iteration limit of 1. So, hmmm...I don't know. To add to the confusion, Solver has to run a few "Finite Differences" on the data to calculate a derivative. This helps in calculating a direction of movement. How these play into the Loop value is a mystery. Dana DeLouis On 10/15/09 8:30 PM, JoeU2004 wrote: "Max" wrote: Try this Tom Ogilvy classic for a way using Solver: http://tinyurl.com/5kx9bw Nice! If Solver cannot find an exact sum (certainly try that first), I would solve for the Min of ABS(D1-1391.03) in Tom's example. But either way, for N numbers, Solver might have to look at 2^N - 1 combinations. In Excel 2003, Solver's Iteration option is limited to 32767. Does that mean this approach might not be suitable when N is more than 15? (Unless you get lucky, of course.) Or am I misunderstanding the Iteration option? ----- original message ----- "Max" wrote in message ... Try this Tom Ogilvy classic for a way using Solver: http://tinyurl.com/5kx9bw -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Michelle" wrote: I have a list of 11 numbers. I need to use some of those numbers to come to a total of $3,144.28. I don't need all of those numbers, but I don't know which ones to exclude. I also don't know how many of the 11 numbers I need. Is there a function or something that would add random numbers in the group until it finds what numbers make up the total I need? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly add numbers in a group
Thanks! This is pretty cool. It didn't work on my data, but I tried it on
same sample lists and it worked on the samples. I guess that probably means that there's a problem with my data. "Max" wrote: Try this Tom Ogilvy classic for a way using Solver: http://tinyurl.com/5kx9bw -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Michelle" wrote: I have a list of 11 numbers. I need to use some of those numbers to come to a total of $3,144.28. I don't need all of those numbers, but I don't know which ones to exclude. I also don't know how many of the 11 numbers I need. Is there a function or something that would add random numbers in the group until it finds what numbers make up the total I need? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly add numbers in a group
Hi. Feel free to send me your data, or post your data here.
We should be able to double check your results. I like to scale financial data by a hundred. IE Multiply the input by 100, and look for a total of 314428. Dana DeLouis On 10/16/09 3:26 PM, Michelle wrote: Thanks! This is pretty cool. It didn't work on my data, but I tried it on same sample lists and it worked on the samples. I guess that probably means that there's a problem with my data. "Max" wrote: Try this Tom Ogilvy classic for a way using Solver: http://tinyurl.com/5kx9bw -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Michelle" wrote: I have a list of 11 numbers. I need to use some of those numbers to come to a total of $3,144.28. I don't need all of those numbers, but I don't know which ones to exclude. I also don't know how many of the 11 numbers I need. Is there a function or something that would add random numbers in the group until it finds what numbers make up the total I need? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly add numbers in a group
It turns out some numbers were missing from my list. Once I added them, it
worked perfectly. Thanks again! "Michelle" wrote: Thanks! This is pretty cool. It didn't work on my data, but I tried it on same sample lists and it worked on the samples. I guess that probably means that there's a problem with my data. "Max" wrote: Try this Tom Ogilvy classic for a way using Solver: http://tinyurl.com/5kx9bw -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Michelle" wrote: I have a list of 11 numbers. I need to use some of those numbers to come to a total of $3,144.28. I don't need all of those numbers, but I don't know which ones to exclude. I also don't know how many of the 11 numbers I need. Is there a function or something that would add random numbers in the group until it finds what numbers make up the total I need? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Randomly sort a set group of integers | Excel Worksheet Functions | |||
How can I convert a group of numbers to a group of letters? | Excel Worksheet Functions | |||
randomly select numbers | Excel Discussion (Misc queries) | |||
to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions | |||
How do I shuffle a group of cells randomly in Excel? | Excel Discussion (Misc queries) |