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

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

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

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

RBS


wrote in message
...
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

Using Excel for statistical analysis - a question forproblem-solvers
 

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.

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

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


wrote in message
...
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.

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

Hi Bart,

There are 1048575 possible combinations then:

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

Regards,
Peter T


"RB Smissaert" wrote in message
...
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


wrote in message
...
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.

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

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


"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,

There are 1048575 possible combinations then:

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

Regards,
Peter T


"RB Smissaert" wrote in message
...
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


wrote in message
...
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.

Using Excel for statistical analysis - a question forproblem-solvers
 

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

Using Excel for statistical analysis - a question forproblem-solvers
 

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

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

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

wrote in message
...
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