Randomly add numbers in a group

[Michelle]

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

 

[Max]

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

 

[JoeU2004]

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 -----

 

[Dana DeLouis]

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

 

[Michelle]

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.

 

[Dana DeLouis]

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

 

[Michelle]

It turns out some numbers were missing from my list. Once I added them, it
worked perfectly. Thanks again!