I have 13 values and I need the sum of 3 combinations.(i.e (13*12*11)/(1*2*3)
totalling to 286 cmbination values.this is to map some value which is hiding
in these combinations.Apart from manally creating this combination ,IS there
any short method/work sheet function which can make this faster?
Please advice.
Balaji

=COMBIN(13,3)

Please remember that Excel has a Help function, and if you'd tried typing
the word "combinations" into there it would have given you a clue.
--
David Biddulph

"Balaji" wrote in message
...
I have 13 values and I need the sum of 3 combinations.(i.e
(13*12*11)/(1*2*3)
totalling to 286 cmbination values.this is to map some value which is
hiding
in these combinations.Apart from manally creating this combination ,IS
there
any short method/work sheet function which can make this faster?
Please advice.
Balaji

Thanks David,
But i need a help to list out the values in 286 rows.
If I list out 13 values in A1 to A13 the total should be starting from
B1=a1+a2+a3,b2=a1+a2+a4,b3=a1+a2+a5.... like wise until B286.ie 286
combination values.
Could you help me?


"David Biddulph" wrote:

=COMBIN(13,3)

Please remember that Excel has a Help function, and if you'd tried typing
the word "combinations" into there it would have given you a clue.
--
David Biddulph

"Balaji" wrote in message
...
I have 13 values and I need the sum of 3 combinations.(i.e
(13*12*11)/(1*2*3)
totalling to 286 cmbination values.this is to map some value which is
hiding
in these combinations.Apart from manally creating this combination ,IS
there
any short method/work sheet function which can make this faster?
Please advice.
Balaji

Maybe the following . . .
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'--
'John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
'Calls Comb2 sub.
'Creates the list in a single column.
'Select the top cell of the column then run code.
Sub Combinations()
Dim n As Variant
Dim m As Variant
ReStart:
n = InputBox("Number of items?", "Combinations")
If Len(n) = 0 Then Exit Sub
m = InputBox("Taken how many at a time?", "Combinations")
If Len(m) = 0 Then GoTo ReStart

Application.ScreenUpdating = False
Comb2 n, m, 1, vbNullString, ActiveCell
Application.ScreenUpdating = True
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
'John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
Sub Comb2(ByVal n As Integer, ByVal m As Integer, ByVal k As Integer, _
ByVal s As String, ByRef rng As Excel.Range)
If m n - k + 1 Then Exit Sub
If m = 0 Then
rng.Value = RTrim$(s)
Set rng = rng(2, 1)
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", rng
Comb2 n, m, k + 1, s, rng
End Sub
'--

Thanks Jim
I think I have to be more precise on to my request.
here we go.
Cell A1 - 26
Cell A2 - 45
Cell A3 - 20

Now I need a formulae which gives the following results from B1 onwards.
B1 should be 26 + 45
B2 should be 26 + 20
B3 should be 45 + 20

So I have 3 results as 71,46 and 65(3 combination results)
As same I i have entered 5 (n)values from A1 to A5 with a combination of
3(combin)
I should get 10 values [(5*4*3)/(1*2*3)]from B1 to B10.

Problem I have is ,for example I have a number 46 which is to be found from
the numbers in A1 to A3.
If I run this macro (required) and then If i do a cntrl + F ,I will find 46
in B2 and I will come to know it is the summation of A1 and A3.
This logic should work out for n numbers and for a given set of combination.
I hope I have interpreted clear.
Thanks.

"Jim Cone" wrote:

Maybe the following . . .
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'--
'John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
'Calls Comb2 sub.
'Creates the list in a single column.
'Select the top cell of the column then run code.
Sub Combinations()
Dim n As Variant
Dim m As Variant
ReStart:
n = InputBox("Number of items?", "Combinations")
If Len(n) = 0 Then Exit Sub
m = InputBox("Taken how many at a time?", "Combinations")
If Len(m) = 0 Then GoTo ReStart

Application.ScreenUpdating = False
Comb2 n, m, 1, vbNullString, ActiveCell
Application.ScreenUpdating = True
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
'John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
Sub Comb2(ByVal n As Integer, ByVal m As Integer, ByVal k As Integer, _
ByVal s As String, ByRef rng As Excel.Range)
If m n - k + 1 Then Exit Sub
If m = 0 Then
rng.Value = RTrim$(s)
Set rng = rng(2, 1)
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", rng
Comb2 n, m, k + 1, s, rng
End Sub
'--

I don't believe I can help you.
--
Jim Cone




"Balaji"
wrote in message
Thanks Jim
I think I have to be more precise on to my request.
here we go.
Cell A1 - 26
Cell A2 - 45
Cell A3 - 20
Now I need a formulae which gives the following results from B1 onwards.
B1 should be 26 + 45
B2 should be 26 + 20
B3 should be 45 + 20
So I have 3 results as 71,46 and 65(3 combination results)
As same I i have entered 5 (n)values from A1 to A5 with a combination of
3(combin)
I should get 10 values [(5*4*3)/(1*2*3)]from B1 to B10.
Problem I have is ,for example I have a number 46 which is to be found from
the numbers in A1 to A3.
If I run this macro (required) and then If i do a cntrl + F ,I will find 46
in B2 and I will come to know it is the summation of A1 and A3.
This logic should work out for n numbers and for a given set of combination.
I hope I have interpreted clear.
Thanks.



"Jim Cone" wrote:
Maybe the following . . .
' --
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
'--
'John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
'Calls Comb2 sub.
'Creates the list in a single column.
'Select the top cell of the column then run code.
Sub Combinations()
Dim n As Variant
Dim m As Variant
ReStart:
n = InputBox("Number of items?", "Combinations")
If Len(n) = 0 Then Exit Sub
m = InputBox("Taken how many at a time?", "Combinations")
If Len(m) = 0 Then GoTo ReStart

Application.ScreenUpdating = False
Comb2 n, m, 1, vbNullString, ActiveCell
Application.ScreenUpdating = True
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
'John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
Sub Comb2(ByVal n As Integer, ByVal m As Integer, ByVal k As Integer, _
ByVal s As String, ByRef rng As Excel.Range)
If m n - k + 1 Then Exit Sub
If m = 0 Then
rng.Value = RTrim$(s)
Set rng = rng(2, 1)
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", rng
Comb2 n, m, k + 1, s, rng
End Sub
'--