"Lars-Åke Aspelin" wrote in message
...
On Wed, 15 Jul 2009 21:21:40 -0700, "Conan Kelly"
wrote:

Cross-posted:
- microsoft.public.excel.programming
- microsoft.public.excel.worksheet.functions




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



---------------------------
"Smokin' weed kills your brain cells. Drinkin' only screws up your
liver...ya got 2 a those."
- Earl Hickey (NBC's "My Name is Earl")


If Milli Vanilli falls in the woods, does someone else make a sound?


There are 2^162-1 different combinations with one or several terms.
This is more than 5*10^48 combinations and thus not a problem that any
existing computer could solve.

Is there some other condition (restriction on the combinations) that
leads up to the 26000 combinations you state that you have?

Lars-Åke

Lars-Åke,

Thank you for the feedback.

It's been a while since I've had a math class (and I didn't go very high in
math classes)...and, although I'm good at math, I'm definitely not a math
wiz. I just assumed that 162^2 was the number of possible combinations.
That is where the 26,000+ combinations came from.

Thanks again,

Conan