Hi. Here are some tips for Solver...

* In column C, set each cell in your range =A*B (e.g. C4 = A4*B4) so
right
now, they will all equal zero
* Set D1= Sum(C:C)

One technique is to removes these two steps and just use:
=SumProduct(A1:A163,B1:B163)


* Set E1 = 0
* Set F1 = 1
*set solver to manipulate B1:B162, with the condition that
B1:B162=E1, add
condition B1:B162<=F1, add condition B1:B162= Integer'

Slightly better is to remove these steps, and instead, add the one
constraint that B1:B163 are "Bin" (meaning 'Binary'). This
automatically limits B:B to 0 or 1 as you intended.

Don't forget to set the options to "Assume Linear" to increase the speed.

I'm only a casual user of the solver add-in, so I don't know if it has
limitations...

One limitation would be 200 Changing cells, but that's no problem here
with 163 changing cells.

= = = =
HTH :)
Dana DeLouis



ker_01 wrote:
I'm only a casual user of the solver add-in, so I don't know if it has
limitations that might affect your outcome, but I just built a simple model
with 10 items with each just present or absent to hit a target value, and it
was fairly fast. Each additional item will slow it down exponentially, so
still expect it to take a while. One thing I like about the VBA solution is
that you can track how many iterations are complete, so you know the machine
isn't locked up and you can guestimate how much longer it will run.

Anyway, back to solver
* Place your values in column A (A1:A162)
* Leave column B blank (solver will be using these cells)
* In column C, set each cell in your range =A*B (e.g. C4 = A4*B4) so right
now, they will all equal zero
* Set D1= Sum(C:C)
* Set E1 = 0
* Set F1 = 1
*set solver to manipulate B1:B162, with the condition that B1:B162=E1, add
condition B1:B162<=F1, add condition B1:B162= Integer' target cell is D1 and
should equal your 4MM value

as a casual user of solver I don't know if this gives you all possible
combinations, or if you just get the first one it matches, because when I use
it I only need one answer.

"Conan Kelly" wrote:

Martin Brown,

If it is a homework question then you were probably meant to compute the
sum of every pair or something like that. N(N-1)/2
If it was a homework problem, I would have a text book to look up the
correct process.

We have a client that has $28,000,000+ in loans in one category in June of
2006. They are telling me that $24,000,000+ are classified wrong and were
reclassified to another category in July 2006. For one reason or another,
they can't/didn't supply a list of loan ID numbers of the misclassified
loans. I was hoping to see if I could come up with a combination of loan
amounts to equal the $24,000,000+ number they supplied.

Thanks again,

Conan





"Martin Brown" wrote in message
...
KC wrote:
I must have misread the requirements.
If I have 4 numbers to sum,
from one single number - I have 4 numbers
from two numbers - I have 4 x 3 numbers
from three numbers - I have 4 x 3 x 2 numbers
But that still includes the same numbers in different orders and since
addition is commutative you can reduce the amount of work needed. It
doesn't help much but it does reduce things slightly from

O( N^(N+1/2) )

to a mere O(2^N )

Either way the computational difficulty of the problem increases
exponentially with the number of numbers you want to combine.

Given N distinct numbers taken M at a time the number of possible sums
that can be made is

s = N!/(M!(N-M!)

For your concrete example of 4 numbers this gives

N 0 1 2 3 4
s 1 4 6 4 1

Which as another poster pointed out yields 2^N-1 values.
why do I have only 2 loops for 162 numbers?
Because you don't have a clue what you are doing.

You would need 161 nested loops or a recursive implementation to do the
problem as stated and it would take a time much greater than the age of
the universe even on the worlds fastest machine.

It is a tractable problem only for relatively modest values of N,M.

If it is a homework question then you were probably meant to compute the
sum of every pair or something like that. N(N-1)/2

Regards,
Martin Brown
first number + 2nd number = 2nd number + first number
So I must also get rid of sums of equal values

Hello all,

I'm looking for an XL solution and/or a VBA solution...and I just can't
wrap my head around this.

I have a list of 162 numbers (loan amounts: min = $0, max =
$3,107,000).

I want to find the sum of every single combination of the 162
numbers...from one single number up to all 162 numbers. If my
calculations are correct, that is 26,244 different sums.

For VBA, I'm guessing I'm gonna need 2 for loops...one nested inside the
other. But I can't figure out the code to sum up all the different
combinations. Something like this:


For plngIndexX = LBound(pdblArray) To UBound(pdblArray)
For plngIndexY = LBound(pdblArray) To UBound(pdblArray)
'code to calculate all the differnt combinations
Next plngIndexY
Next plngIndexX


In XL, trying to think like a computer...I'm gonna need 162 bits(cells)
to calculate. But instead of each bit having a progressive value of a
power of 2, each bit will be one of the numbers in my list. I was
thinking of using the list of numbers as column headers across the top,
have a sumproduct formula in column 163/164 that will sum up the column
headers according to the bits being on or off, and then have 26,000+
rows of bits marked on or off. The problem would be filling out those
26,000 rows with 1's & 0's. Anyone know of formulas or code that I could
use to mark my bits on or off?

If it would be easier/faster in MS Access/SQL Server, I'm all ears.

Thanks for any help anyone can provide,

Conan Kelly