Get summary of combinations could be made with each sum?

[Kishan]

Using Excel 2000

Hi,

I got numbers in cell D5:Q7 distributed in the 3 rows 1 To 42
I need to make combinations picking 14 numbers out of 42 numbers these make combinations from Minimum sum with 287 and Maximum sum with 315

My query is how much combinations could be made with each of sum from 287 to 315
With 287 is only 1 possible
How much with 288?
How much with 289?
How much with 289?
How much with 300?
And so on till 315...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2
3
4			Num	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14		Sum		Sum	Combinations
5			GR-1	1	4	7	10	13	16	19	22	25	28	31	34	37	40		287		287	1
6			GR-2	2	5	8	11	14	17	20	23	26	29	32	35	38	41		301		288	?
7			GR-3	3	6	9	12	15	18	21	24	27	30	33	36	39	42		315		289	?
8																					290	?
9																					291	?
10																					292	?
11																					293	?
12																					294	?
13																					295	?
14																					296	?
15																					297	?
16																					298	?
17																					299	?
18																					300	?
19																					301	1
20																					302	?
21																					303	?
22																					304	?
23																					305	?
24																					306	?
25																					307	?
26																					308	?
27																					309	?
28																					310	?
29																					311	?
30																					312	?
31																					313	?
32																					314	?
33																					315	1
34
35
36
Sheet2

Thank you in advance

Regards,
Kishan

 

[Kishan]

Here is the histogram of totals.

Hi shg, thank you for doing a so hard work I really appreciate it.

May I do not explain the question correctly? Here is my attempt again

There are 14 sets (each set contain 3 Numbers) and
There are 3 groups (each group contain 14 Numbers) as follow


Group-1 contain numbers 1, 4, 7, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37 & 40

Group-2 contain numbers 2, 5, 8, 11, 14, 17, 20, 23, 26, 29, 32, 35, 38 & 41

Group-3 contain numbers 3, 6, 9, 12, 15, 18, 21, 24, 27, 30, 33, 36, 39 & 42

Set-1 contain numbers 1, 2 & 3
Set-2 contain numbers 4, 5 & 6
Set-3 contain numbers 7, 8 & 9
Set-4 contain numbers 10, 11 & 12
Set-5 contain numbers 13, 14 & 15
Set-6 contain numbers 16, 17 & 18
Set-7 contain numbers 19, 20 & 21
Set-8 contain numbers 22, 23 & 24
Set-9 contain numbers 25, 26 & 27
Set-10 contain numbers 28, 29 & 30
Set-11 contain numbers 31, 32 & 33
Set-12 contain numbers 34, 35 & 36
Set-13 contain numbers 37, 38 & 39
Set-14 contain numbers 40, 41 & 42

The combinations must be made of using the 14 numbers for an each single combination

But the condition: is it can pick only 1 number at a time from the each of the 14 sets.

So if we make a first combination will be with the sum 287
1, 4, 7, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37 & 40 = 287

And the last one will be with the sum 315
3, 6, 9, 12, 15, 18, 21, 24, 27, 30, 33, 36, 39 & 42 = 315

So far I think sum consist between min and max = 278 to 315

Some few more example are shown in the example sheet below if any clarification please I will try my best

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4				Set-1	Set-2	Set-3	Set-4	Set-5	Set-6	Set-7	Set-8	Set-9	Set-10	Set-11	Set-12	Set-13	Set-14
5
6			Group-1	1	4	7	10	13	16	19	22	25	28	31	34	37	40
7
8			Group-2	2	5	8	11	14	17	20	23	26	29	32	35	38	41
9
10			Group-3	3	6	9	12	15	18	21	24	27	30	33	36	39	42
11
12
13																		Sum of combination
14			Combination 1	1	4	7	10	13	16	19	22	25	28	31	34	37	40	287
15			Combination 2	1	4	7	10	13	16	19	22	25	28	31	34	37	41	288
16			Combination 3	1	4	7	10	13	16	19	22	25	28	31	34	37	42	289
17			Combination 4	1	4	7	10	13	16	19	22	25	28	31	34	38	40	288
18			Combination 5	1	4	7	10	13	16	19	22	25	28	31	34	39	40	289
19			Combination 6	1	4	7	10	13	16	19	22	25	28	31	35	37	40	288
20			Combination 7	1	4	7	10	13	16	19	22	25	28	31	36	37	40	289
21			Combination 8	1	4	7	10	13	16	19	22	25	28	32	34	37	40	288
22			Combination 9	1	4	7	10	13	16	19	22	25	28	33	34	37	40	289
23			Combination 10	1	4	7	10	13	16	19	22	25	29	31	34	37	40	288
24			Combination 11	1	4	7	10	13	16	19	22	25	30	31	34	37	40	289
25			Combination 12	1	4	7	10	13	16	19	22	26	28	31	34	37	40	288
26			Combination 13	1	4	7	10	13	16	19	22	27	28	31	34	37	40	289
27			Combination 14	1	4	7	10	13	16	19	23	25	28	31	34	37	40	288
28			Combination 15	1	4	7	10	13	16	19	24	25	28	31	34	37	40	289
29			Combination 16	1	4	7	10	13	16	20	22	25	28	31	34	37	40	288
30			Combination 17	1	4	7	10	13	16	21	22	25	28	31	34	37	40	289
31			Combination 18	1	4	7	10	13	17	19	22	25	28	31	34	37	40	288
32			Combination 19	1	4	7	10	13	18	19	22	25	28	31	34	37	40	289
Sheet3

Thank you for your assistance and time

Regards,
Kishan

 

[shg]

If you look at the table I posted, there are over 20 billion combinations that sum between 278 and 315.

	100 ​	200 ​	300 ​
00		14,064,454​
01		15,197,009​
02		16,403,722​
03		17,689,441​
04		19,056,543​
05	1​	20,510,093​
06	1​	22,052,498​
07	2​	23,689,046​
08	3​	25,422,131​
09	5​	27,257,222​
10	7​	29,196,682​
11	11​	31,246,116​
12	15​	33,407,807​
13	22​	35,687,484​
14	30​	38,087,277​
15	42​	40,612,987​
16	56​	43,266,574​	514,775,571​
76	1,580,807​	449,329,331​	77,689,707​
77	1,755,607​	456,893,130​	73,553,760​
78	1,946,940​	69,578,665​
79	2,156,600​	65,764,357​
80	2,385,536​	62,105,494​
81	2,635,742​	58,601,481​
82	2,908,290​	55,246,778​
83	3,205,408​	52,040,353​
84	3,528,289​	48,976,506​
85	3,879,400​	46,053,843​
86	4,260,075​	43,266,574​
87	4,673,020​	40,612,987​
88	5,119,712​	38,087,277​
89	5,603,124​	35,687,484​
90	6,124,858​	33,407,807​
91	6,688,158​	31,246,116​
92	7,294,777​	29,196,682​
93	7,948,218​	27,257,222​
94	8,650,368​	25,422,131​
95	9,405,017​	23,689,046​
96	10,214,170​	22,052,498​
97	11,081,891​	20,510,093​
98	12,010,311​	19,056,543​
99	13,003,753​	17,689,441​

[td="bgcolor:#FFFF00"]

555,356,419​

[/td] [td="bgcolor:#FFFF00"]

555,546,058​

[/td] [td="bgcolor:#FFFF00"]

555,356,419​

[/td] [td="bgcolor:#FFFF00"]

554,794,743​

[/td] [td="bgcolor:#FFFF00"]

553,855,273​

[/td] [td="bgcolor:#FFFF00"]

552,546,702​

[/td] [td="bgcolor:#FFFF00"]

550,864,741​

[/td] [td="bgcolor:#FFFF00"]

548,819,521​

[/td] [td="bgcolor:#FFFF00"]

546,408,202​

[/td] [td="bgcolor:#FFFF00"]

543,642,289​

[/td] [td="bgcolor:#FFFF00"]

540,520,348​

[/td] [td="bgcolor:#FFFF00"]

537,055,199​

[/td] [td="bgcolor:#FFFF00"]

533,246,758​

[/td] [td="bgcolor:#FFFF00"]

529,109,074​

[/td] [td="bgcolor:#FFFF00"]

524,643,328​

[/td] [td="bgcolor:#FFFF00"]

519,864,702​

[/td] [td="bgcolor:#FFFF00"]

464,260,655​

[/td] [td="bgcolor:#FFFF00"]

471,425,344​

[/td] [td="bgcolor:#FFFF00"]

478,368,257​

[/td] [td="bgcolor:#FFFF00"]

485,083,406​

[/td] [td="bgcolor:#FFFF00"]

491,552,326​

[/td] [td="bgcolor:#FFFF00"]

497,769,746​

[/td] [td="bgcolor:#FFFF00"]

503,717,820​

[/td] [td="bgcolor:#FFFF00"]

509,392,127​

[/td] [td="bgcolor:#FFFF00"]

514,775,571​

[/td] [td="bgcolor:#FFFF00"]

519,864,702​

[/td] [td="bgcolor:#FFFF00"]

524,643,328​

[/td] [td="bgcolor:#FFFF00"]

529,109,074​

[/td] [td="bgcolor:#FFFF00"]

533,246,758​

[/td] [td="bgcolor:#FFFF00"]

537,055,199​

[/td] [td="bgcolor:#FFFF00"]

540,520,348​

[/td] [td="bgcolor:#FFFF00"]

543,642,289​

[/td] [td="bgcolor:#FFFF00"]

546,408,202​

[/td] [td="bgcolor:#FFFF00"]

548,819,521​

[/td] [td="bgcolor:#FFFF00"]

550,864,741​

[/td] [td="bgcolor:#FFFF00"]

552,546,702​

[/td] [td="bgcolor:#FFFF00"]

553,855,273​

[/td] [td="bgcolor:#FFFF00"]

554,794,743​

[/td]

What is it you want now?

 

[Kishan]

If you look at the table I posted, there are over 20 billion combinations that sum between 278 and 315.
What is it you want now?

Hi shg,

Really I cannot understand with sum 287 you are getting 464,260,655 sets of combinations when I get only 1 with sum 287 where is the error

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
13																Sum of combination
14	Combination 1	1	4	7	10	13	16	19	22	25	28	31	34	37	40	287
Sheet3

Please can you make few combinations may 4 or 5 with 14 numbers using always 1 number from the each of the 14 sets



Regards,
Kishan

 

[shg]

Kishan, I don't understand the whole "14 sets" thing.

I think it's time for you to learn to program

 

[Kishan]

Kishan, I don't understand the whole "14 sets" thing.

I think it's time for you to learn to program

Hi shg, I imagine May be my way of explanation is not clear if I could you can solve it defiantly. I do appreciate your help and time.

6/49 make set of 6 out of 49 (only 1 set of numbers from 1 to 49) numbers which make 13.983.816 set of 6 numbers, Formula =COMBIN(49,6)

Here are 3 groups of 14 numbers & each group has set of 3 numbers
Combination are made "set of 14 numbers" (14 numbers but picking 1 number from each set only)

Or may be in other words I can say numbers are divided in 3 rows and 14 columns
Each row contain 14 numbers
And the each column contain set of 3 numbers

So far make all possible combinations picking only 1 number from the each set
Number can be picked any of 3 rows

If 14 numbers are picked from 1st row, rest 2 rows number will be remain out
If 13 numbers are picked from 1st row (say from set 1 through set 13), in this case complete the set with 14 numbers, pick any of 1 number only from the set 14 which has 3 numbers 40, 41 or 42

Thank you

Regards,
Kishan

 

[Eric W]

	G	H
Value wanted	# of ways

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]287[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]288[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]289[/TD]
[TD="align: right"]105[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]546[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]291[/TD]
[TD="align: right"]2184[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]7098[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]293[/TD]
[TD="align: right"]19383[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]45474[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]295[/TD]
[TD="align: right"]93093[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]296[/TD]
[TD="align: right"]168168[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]297[/TD]
[TD="align: right"]270270[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]298[/TD]
[TD="align: right"]388752[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]299[/TD]
[TD="align: right"]502593[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]585690[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]301[/TD]
[TD="align: right"]616227[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]585690[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]303[/TD]
[TD="align: right"]502593[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]304[/TD]
[TD="align: right"]388752[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]305[/TD]
[TD="align: right"]270270[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]306[/TD]
[TD="align: right"]168168[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]307[/TD]
[TD="align: right"]93093[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]45474[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]309[/TD]
[TD="align: right"]19383[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]310[/TD]
[TD="align: right"]7098[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]311[/TD]
[TD="align: right"]2184[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]312[/TD]
[TD="align: right"]546[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]313[/TD]
[TD="align: right"]105[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]314[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]315[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet3​

 

[Kishan]

	G	H
Value wanted	# of ways

<tbody>
[TD="align: center"]6
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]287
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]288
[/TD]
[TD="align: right"]14
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]289
[/TD]
[TD="align: right"]105
[/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]290
[/TD]
[TD="align: right"]546
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"]291
[/TD]
[TD="align: right"]2184
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"]292
[/TD]
[TD="align: right"]7098
[/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"]293
[/TD]
[TD="align: right"]19383
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"]294
[/TD]
[TD="align: right"]45474
[/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"]295
[/TD]
[TD="align: right"]93093
[/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"]296
[/TD]
[TD="align: right"]168168
[/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"]297
[/TD]
[TD="align: right"]270270
[/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"]298
[/TD]
[TD="align: right"]388752
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"]299
[/TD]
[TD="align: right"]502593
[/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"]300
[/TD]
[TD="align: right"]585690
[/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"]301
[/TD]
[TD="align: right"]616227
[/TD]

[TD="align: center"]22
[/TD]
[TD="align: right"]302
[/TD]
[TD="align: right"]585690
[/TD]

[TD="align: center"]23
[/TD]
[TD="align: right"]303
[/TD]
[TD="align: right"]502593
[/TD]

[TD="align: center"]24
[/TD]
[TD="align: right"]304
[/TD]
[TD="align: right"]388752
[/TD]

[TD="align: center"]25
[/TD]
[TD="align: right"]305
[/TD]
[TD="align: right"]270270
[/TD]

[TD="align: center"]26
[/TD]
[TD="align: right"]306
[/TD]
[TD="align: right"]168168
[/TD]

[TD="align: center"]27
[/TD]
[TD="align: right"]307
[/TD]
[TD="align: right"]93093
[/TD]

[TD="align: center"]28
[/TD]
[TD="align: right"]308
[/TD]
[TD="align: right"]45474
[/TD]

[TD="align: center"]29
[/TD]
[TD="align: right"]309
[/TD]
[TD="align: right"]19383
[/TD]

[TD="align: center"]30
[/TD]
[TD="align: right"]310
[/TD]
[TD="align: right"]7098
[/TD]

[TD="align: center"]31
[/TD]
[TD="align: right"]311
[/TD]
[TD="align: right"]2184
[/TD]

[TD="align: center"]32
[/TD]
[TD="align: right"]312
[/TD]
[TD="align: right"]546
[/TD]

[TD="align: center"]33
[/TD]
[TD="align: right"]313
[/TD]
[TD="align: right"]105
[/TD]

[TD="align: center"]34
[/TD]
[TD="align: right"]314
[/TD]
[TD="align: right"]14
[/TD]

[TD="align: center"]35
[/TD]
[TD="align: right"]315
[/TD]
[TD="align: right"]1
[/TD]

</tbody>

Sheet3​

Thank you Eric, for understanding my view point I am sure this is the correct what I were asking for you nailed it.

Can you provide a formula? If it is possible for you

Also I want to know can you make a VBA that can make combinations, by individually sum for example with 287, 289 or with 302...

I do appreciate your assistance

Regards,
Kishan

 

[Eric W]

I'm sure there's a formula, but I don't have time to derive it. I generated the results by just iterating through the 4,782,969 possibilities and counting the ones that match your value.

Public Function CountCombs(ByVal x As Long)
Dim ix(1 To 14) As Long, i As Long
    
    For i = 1 To 14
        ix(i) = (i - 1) * 3 + 1
    Next i
MyLoop:
    If WorksheetFunction.Sum(ix) = x Then CountCombs = CountCombs + 1
    For i = 1 To 14
        ix(i) = ix(i) + 1
        If ix(i) Mod 3 <> 1 Then Exit For
        ix(i) = ix(i) - 3
    Next i
    If i < 15 Then GoTo MyLoop:


End Function

Put that in a general Module, and you can call it by putting this in H7:

=CountCombs(G7)


You can drag that down to H35, but it's slow. Knowing that it's symmetric, you can go down just to H21. Still, you might just want to put it in and go get a tasty beverage while you wait.

I can see ways to speed it up, but since this is kind of a 1-of-a-kind thing, I don't see the need. Like shg, I don't see the need to enumerate all the possibilities that match. No possible value. But you have your answer.

Good luck.

 

[Kishan]

I'm sure there's a formula, but I don't have time to derive it. I generated the results by just iterating through the 4,782,969 possibilities and counting the ones that match your value.

I can see ways to speed it up, but since this is kind of a 1-of-a-kind thing, I don't see the need. Like shg, I don't see the need to enumerate all the possibilities that match. No possible value. But you have your answer.

Good luck.

Thank you Eric, yes I got the answer of my opening post and it is correct. Also thank you for giving the function it is working slowly as you said, but all the way it giving the answers correct.

I appreciate your time and help

Good Luck to you

Regards,
Kishan