Using Excel for statistical analysis - a question for problem-solvers

[mrsethbullock]

I have a business analytics problem and am looking for some advice.

I have 20 variables, each of which has a value assigned to it (Var1 =
5, Var2 = 8, Var3 = 6, Var4 = 10, Var5 = 2 and so on).

All of these 20 variables can exist in isolation or they can exist
with any combination of the other 19 variables.

What I would like to do is to be able to see all the possible
combinations of these variables in order to see the total values of
different combinations. For example, if one combination is
(Var1+Var2+Var3+Var4 then the value would be 29).

Can anyone suggest how I would go about doing this? By hand, I figure
I would have to make thousands upon thousands of equations (if not
more). So obviously, I'm trying to find a way to automate the
process.

I am well versed in MS Excel and Access. I know a bit of VB, but not
much.

Any help would be appreciated.

SB

 

[RB Smissaert]

Do the variables have to be unique in the combination or can there be
duplicates?

RBS

 

[mrsethbullock]

I am not sure precisely what you are asking. Hopefully, one of the
following points will help clarify my problem (sorry!)"

1. I would never use any single variable twice in a single
combination. There would never be a Var5+Var5+Var+1.

2. The order of the variables in a combination is irrelevent. For my
purposes the string Var1+Var2+Var3 is equal to both Var3+Var2+Var1 and
Var2+Var3+Var1. If, however, for programming purposes the duplicates
could not be removed, I could probably make do with them.

I hope that makes it a bit clearer.

 

[RB Smissaert]

There are 1048575 possible combinations then:

Sub Test()

Dim lItems As Long
Dim i As Long
Dim lCombinations As Long

lItems = 20

For i = 1 To lItems
lCombinations = lCombinations + _
Application.WorksheetFunction.Combin(lItems, i)
Next i

MsgBox lCombinations, , _
"unique combinations with " & lItems & " items"

End Sub


RBS

 

[Peter T]

Hi Bart,

There are 1048575 possible combinations then:

looks remarkably like
items = 20
= 2 ^ items - 1

Regards,
Peter T

 

[RB Smissaert]

Hi Peter,

Yes, you are right there, but the loop is useful if you want a fixed number
of items less than the
total number of all items as then it won't be a simple calculation.

RBS

 

[mrsethbullock]

Thanks -- much appreciated!

Any ideas on how I would go about creating a script that would
calculate the totals of all 1048575 variables in an Excel
spreadsheet? I have a worksheet with two simple columns - one is
called variables, the other is called value. I am trying to figure
out how to manipulate that data.


Thanks!!

SB

 

[mrsethbullock]

Thanks -- much appreciated!

Any ideas on how I would go about creating a script that would
calculate the totals of all 1048575 variables in an Excel
spreadsheet? I have a worksheet with two simple columns - one is
called variables, the other is called value. I am trying to figure
out how to manipulate that data.


Thanks!!

SB

 

[RB Smissaert]

This is a simple way of doing it:

Sub test()

Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim f As Long
Dim n As Long
Dim UBV As Long
Dim arrVars
Dim arrCombin

arrVars = Array("1", "2", "3", "4", "5", "6", "7")
UBV = UBound(arrVars)
ReDim arrCombin(0 To (2 ^ (UBV + 1)) - 2)

MsgBox (2 ^ (UBV + 1)) - 1, , _
"possible combinations"

'1 item
For a = 0 To UBV
arrCombin(n) = arrVars(a)
n = n + 1
Next a

'2 items
For a = 0 To UBV - 1
For b = a + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b)
n = n + 1
Next b
Next a

'3 items
For a = 0 To UBV - 2
For b = a + 1 To UBV - 1
For c = b + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b) & _
arrVars(c)
n = n + 1
Next c
Next b
Next a

'4 items
For a = 0 To UBV - 3
For b = a + 1 To UBV - 2
For c = b + 1 To UBV - 1
For d = c + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b) & _
arrVars(c) & _
arrVars(d)
n = n + 1
Next d
Next c
Next b
Next a

'5 items
For a = 0 To UBV - 4
For b = a + 1 To UBV - 3
For c = b + 1 To UBV - 2
For d = c + 1 To UBV - 1
For e = d + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b) & _
arrVars(c) & _
arrVars(d) & _
arrVars(e)
n = n + 1
Next e
Next d
Next c
Next b
Next a

'6 items
For a = 0 To UBV - 5
For b = a + 1 To UBV - 4
For c = b + 1 To UBV - 3
For d = c + 1 To UBV - 2
For e = d + 1 To UBV - 1
For f = e + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b) & _
arrVars(c) & _
arrVars(d) & _
arrVars(e) & _
arrVars(f)
n = n + 1
Next f
Next e
Next d
Next c
Next b
Next a

'all items, only one combination possible
'(as order doesn't alter)
For a = 0 To UBV
If a = 0 Then
arrCombin(n) = arrVars(a)
Else
arrCombin(n) = arrCombin(n) & arrVars(a)
End If
Next a

Columns(1).Clear

For n = 0 To UBound(arrCombin)
Cells(n + 1, 1) = arrCombin(n)
Next n

End Sub

Just extend to 20 and alter the concatenation to adding variables.
I ams sure there is a much more elegant way to do this (maybe a recursive
Sub), but
this will do.


RBS

 

[cthwaites]

Thanks RBS. I have spent a few hours studying your code and think I
follow most of it.

One further question (sorry... I fear this is when the tomatoes will
be thrown at me)... if I wanted to assign each option (a, b, c, d, e,
etc) a numerical value, any suggestions on how I would go about doing
this?

Ultimately, I am trying to achieve two things:

- list all the possible combinations
- ranks all the possible combinations (based on the idea that each
letter has a numerical value assigned to it).


p.s. - do you know of any good VB books that you could recommend?


Thanks,
SB

 

[RB Smissaert]

if I wanted to assign each option (a, b, c, d, e,
etc) a numerical value, any suggestions on how I would go about doing
this?

Yes, you could do:

Dim arr(0 to 6) As Long

arr(0) = 3
arr(1) = 5
arr(2) = 10
etc.

Instead of concatenating strings as in my code you could add up instead.
So you would get:

arrCombin(n) = arr(a) + arr(b) +arr(c)
etc.

Books is difficult to say as it will depend on our specifics. Maybe go for
the big Walkenbach book.

RBS

 

[gimme_this_gimme_that]

Well if Var1,Var2,Var3, and Var4 never change then this brute force
solution of listing all combinations will work for you.

If these variables vary - then you should post on a Operations
Research news group
(dynamic programming is a topic of Operations Research) news group and
ask an algorithic solution. You can work backwards from 29 and then
get a solution list. Probably in milliseconds using VBA.

Dynamic programming is not to be confused with computer programming.
Tee hee.

This enumeration is the classic beginners introduction to dynamic
programming. It's what you read on the first week of class.

Don't be suprised if it takes a few days for you to "get" it. Dynamic
programs are hard to graps if you haven't been introduced to them.

OTHO - if you don't know VBA well enough to program the solution on
your own (once you have it) - then you have no choice - brute force
enumeration is the only thing you can do.

Note that enumeration is very slow! But good enough for personal use
for 29!

 

[Dana DeLouis]

... in order to see the total values of different combinations.

If you have the unique numbers 1-20, then your problem could be simplified
You could use the equations for Unique Integer Partitions.
It won' list them, just count them.
The totals could only be 1 thru 210.
There are 231 combinations that total 29.
There are 15,029 that total 100,
etc...