find the correct sum

HI EXPERTS,

i hope you and excel can do this.
i have a master data with so many rows.
i.e A B C D
No Name City Values
1 - - 100
2 - - 3081
-
5000 - - 6

now my bos ask me to find values with total sum i.e 258,000 from data above.

can excel do this, or should create program using vb?
can anybody help me?

hope you understand with what i want to achieve.

thanks

regards,
reza

Try this Tom Ogilvy classic for a way using Solver:
http://tinyurl.com/5kx9bw
Link above still works, I just tested
--
Max
Singapore
---
"reza" wrote:
HI EXPERTS,

i hope you and excel can do this.
i have a master data with so many rows.
i.e A B C D
No Name City Values
1 - - 100
2 - - 3081
-
5000 - - 6

now my bos ask me to find values with total sum i.e 258,000 from data above.

can excel do this, or should create program using vb?
can anybody help me?

hope you understand with what i want to achieve.

thanks

regards,
reza

Try this Tom Ogilvy classic for a way using Solver:

As a side note, Solver is limited to 200 changing cells.
I may be wrong, but it appears the op is looking among 5000 numbers.
It may require a mixture of approaches.
= = = = = =
HTH :)
Dana DeLouis

On 5/21/10 3:11 AM, Max wrote:
Try this Tom Ogilvy classic for a way using Solver:
http://tinyurl.com/5kx9bw
Link above still works, I just tested
-- Max Singapore --- "reza" wrote:
HI EXPERTS,

i hope you and excel can do this.
i have a master data with so many rows.
i.e A B C D
No Name City Values
1 - - 100
2 - - 3081
-
5000 - - 6

now my bos ask me to find values with total sum i.e 258,000 from data above.

can excel do this, or should create program using vb?
can anybody help me?

hope you understand with what i want to achieve.

thanks

regards,
reza

It may require a mixture of approaches ..

Such as ... ? Any links to share with us?

Hi. No, I'm afraid not. I just wanted to point out that Solver was
limited to 200 changing cells vs the op's input of 5000 numbers.
There's no easy solution, but if one wanted to use Excel's Solver, here
are some general ideas one "may" be able to work with.
It really depends on the data.

Suppose among the 5000 data, one had...
{25, 50, 75, 100, 125, 150, 175, 200, 225, 250...etc}

These would take up 10+ Binary changing cells among the 200 one can use.
One 'could' remove these numbers from the list and write the equation:
=25*x
where 'x' is now an "Integer" constraint (vs binary) with the added
constraint that places limits on the possible values.
(ie x=10 for an upper limit of 250. Option of 'Assume non-negative for
the lower value)

This would replace multiple changing cells with just 1.

Same concept if one had duplicates. If one had 20 of the number 100,
one could write = 100*x, subject to x=20

Some other not so great ideas might be to break the 5000 numbers into
groups of 25 numbers (having 200 groups). Total each group.
You now have 200 numbers. Now use the Binary technique that finds a
total that is Minimized, subject to the constraint that the total is =
258,000.

Suppose the closest you get is 258,500.
Now, look at each group that was picked, and see if you can spot a
combination that totals 500 that you can remove from the list.
Again, it's not a easy problem when you have 5,000 numbers to work with.
It can be more of an art than a science at this point.

= = = = = =
Dana DeLouis

On 5/22/10 6:10 PM, Max wrote:
It may require a mixture of approaches ..

Such as ... ? Any links to share with us?

Thanks for your posting your standard response, Nick. Rich stuff. I'd find
it a little disconcerting though, that OP has chosen to remain strangely
silent despite the wealth of responses given to his query.

Thanks for responding further, Dana. Useful ideas, those. From your
experience, what would be the other usual business applications of this
technique beyond matching payments/partial payments to invoices/accounts?

Hi. I think there are many different applications.
For example, suppose on had the following numbers:

{29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71}

These could be asset values that are to be invested, but perhaps not to
invest more than say 350.
Or, these could be length of parts that are to be put together not to
exceed 350.
Well, there are no solutions at 350. But, one does have 10 possible
solutions that equal 349. For example...
{29, 31, 37, 53, 61, 67, 71}

There are 9 at 351. For example...
{29, 31, 41, 53, 59, 67, 71}

When working with Financial data, as in the other example, I will often
scale the data by 100 to make them all integer values.
(Instead of $1.34, I use 134, and scale the Value sought by the same.)
(This simplifies my code anyway)

When one is testing new code to find "All" the combinations that total a
value, here is something I find interesting that is not often mentioned.
When testing new code for speed, I find that one really doesn't know
if the solution is correct. Therefore, I run it against some known
solutions by only using the numbers 1,2,3,...n

Suppose I only used the numbers 1 thru 20.
n = 20

? n*(n + 1)/4
105

The question is:
How many solutions equal 105 ? (ie the Max)

The solution is surprisingly 15272

This was a fun and interesting programming problem a while ago.
It turns out to be very fast in Vba.
The question is:
Given the numbers 1,2,...100
How many combinations (or subsets) equal 2525?

Off hand, I would have guessed 200, 300, something like that.
There a

1,731,024,005,948,725,016,633,786,324

Anyway, always an interesting subject. :)

So, when the op is working with 5,000 numbers, we can tell it's a hard
problem.

= = = = = =
Dana DeLouis

On 5/23/10 7:00 PM, Max wrote:
Thanks for responding further, Dana. Useful ideas, those. From your
experience, what would be the other usual business applications of this
technique beyond matching payments/partial payments to invoices/accounts?

Thanks for sharing your experiences, Dana