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]

?

 

[Kishan]

Hi,

Here is a bit detail example...
Here are 3 groups of number each group GR-1, GR-2, GR-3 Contain 14 Numbers
It has to make each combination using 14 numbers does not matter it can pick 14 number from any of three groups to match the sum target

I tried to make manually 9 combinations like
1 with sum = 287
2 with sum = 288
3 with sum = 289
1 with sum = 291

It is not possible to make all manually and count them by sum

Please need help

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			1	1	4	7	10	13	16	19	22	25	28	31	34	37	40		287		295	?
14			2	1	4	7	10	13	16	19	22	25	28	31	34	37	41		288		296	?
15			3	1	4	7	10	13	16	19	22	25	28	31	34	37	42		289		297	?
16			4	1	5	7	10	13	16	19	22	25	28	31	34	37	40		288		298	?
17			5	1	5	7	10	13	16	19	22	25	28	31	34	37	41		289		299	?
18			6	1	5	7	10	13	16	19	22	25	28	31	34	37	42		290		300	?
19			7	1	6	7	10	13	16	19	22	25	28	31	34	37	40		289		301	1
20			8	1	6	7	10	13	16	19	22	25	28	31	34	37	41		290		302	?
21			9	1	6	7	10	13	16	19	22	25	28	31	34	37	42		291		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

Regards,
Kishan

 

[Kishan]

Hi,

Or may be any formula so I can generate all sum combinations (one by one sum) and count them manually?

Please need help

Regards,
Kishan

 

[Kishan]

Bump

 

[shg]

The answers are in the hundreds of millions, Kishan. What would you do with them?

 

[Kishan]

The answers are in the hundreds of millions, Kishan. What would you do with them?

Hi shg, you are correct, but I want to know how these hundreds of millions will break with in sum between min = 287 & max 315 in the range 1 = 287 to 29 = 315

Thank you for your help

Regards,
Kishan

 

[Kishan]

The answers are in the hundreds of millions, Kishan. What would you do with them?

Hi shg,

I am still thinking if the 14 combinations could be made out of 42 numbers than =COMBIN(42,14) will create total combinations 52.860.229.080

But here are the 3 groups of 14 numbers so I think this will make like (3^14)
3x3x3x3x3x3x3x3x3x3x3x3x3x3 = 4.782.969, May I am wrong

What do you think?

Regards,
Kishan

 

[Kishan]

Hi,

May someone can help making a VBA to generate all possible combinations using 3 groups with length of 14 in 14 with limit individually from sum 287 to 315

Thank you

Regards,
Kishan

 

[shg]

Here is the histogram of totals.

	A​	B​	C​	D​	E​
1​		100​	200​	300​	400​
2​	00		14,064,454​	555,356,419​	16,403,722​
3​	01		15,197,009​	555,546,058​	15,197,009​
4​	02		16,403,722​	555,356,419​	14,064,454​
5​	03		17,689,441​	554,794,743​	13,003,753​
6​	04		19,056,543​	553,855,273​	12,010,311​
7​	05	1​	20,510,093​	552,546,702​	11,081,891​
8​	06	1​	22,052,498​	550,864,741​	10,214,170​
9​	07	2​	23,689,046​	548,819,521​	9,405,017​
10​	08	3​	25,422,131​	546,408,202​	8,650,368​
11​	09	5​	27,257,222​	543,642,289​	7,948,218​
12​	10	7​	29,196,682​	540,520,348​	7,294,777​
13​	11	11​	31,246,116​	537,055,199​	6,688,158​
14​	12	15​	33,407,807​	533,246,758​	6,124,858​
15​	13	22​	35,687,484​	529,109,074​	5,603,124​
16​	14	30​	38,087,277​	524,643,328​	5,119,712​
17​	15	42​	40,612,987​	519,864,702​	4,673,020​
18​	16	56​	43,266,574​	514,775,571​	4,260,075​
19​	17	77​	46,053,843​	509,392,127​	3,879,400​
20​	18	101​	48,976,506​	503,717,820​	3,528,289​
21​	19	135​	52,040,353​	497,769,746​	3,205,408​
22​	20	175​	55,246,778​	491,552,326​	2,908,290​
23​	21	229​	58,601,481​	485,083,406​	2,635,742​
24​	22	293​	62,105,494​	478,368,257​	2,385,536​
25​	23	378​	65,764,357​	471,425,344​	2,156,600​
26​	24	478​	69,578,665​	464,260,655​	1,946,940​
27​	25	608​	73,553,760​	456,893,130​	1,755,607​
28​	26	762​	77,689,707​	449,329,331​	1,580,807​
29​	27	957​	81,991,577​	441,588,517​	1,421,717​
30​	28	1,188​	86,458,870​	433,677,709​	1,276,741​
31​	29	1,478​	91,096,300​	425,616,328​	1,145,153​
32​	30	1,819​	95,902,709​	417,411,688​	1,025,546​
33​	31	2,241​	100,882,424​	409,083,245​	917,295​
34​	32	2,738​	106,033,550​	400,638,485​	819,153​
35​	33	3,345​	111,359,943​	392,096,721​	730,590​
36​	34	4,056​	116,858,927​	383,465,483​	650,513​
37​	35	4,918​	122,533,814​	374,763,816​	578,466​
38​	36	5,924​	128,381,075​	365,999,154​	513,502​
39​	37	7,132​	134,403,445​	357,190,143​	455,238​
40​	38	8,539​	140,596,460​	348,343,999​	402,843​
41​	39	10,213​	146,962,214​	339,478,833​	356,006​
42​	40	12,156​	153,495,293​	330,601,552​	314,010​
43​	41	14,454​	160,197,084​	321,729,614​	276,592​
44​	42	17,109​	167,061,151​	312,869,486​	243,139​
45​	43	20,228​	174,088,171​	304,037,893​	213,440​
46​	44	23,822​	181,270,644​	295,240,787​	186,965​
47​	45	28,017​	188,608,483​	286,494,035​	163,548​
48​	46	32,832​	196,093,117​	277,803,005​	142,738​
49​	47	38,424​	203,723,667​	269,182,646​	124,400​
50​	48	44,819​	211,490,470​	260,637,657​	108,156​
51​	49	52,207​	219,391,864​	252,182,012​	93,901​
52​	50	60,630​	227,417,067​	243,819,692​	81,311​
53​	51	70,311​	235,563,628​	235,563,628​	70,311​
54​	52	81,311​	243,819,692​	227,417,067​	60,630​
55​	53	93,901​	252,182,012​	219,391,864​	52,207​
56​	54	108,156​	260,637,657​	211,490,470​	44,819​
57​	55	124,400​	269,182,646​	203,723,667​	38,424​
58​	56	142,738​	277,803,005​	196,093,117​	32,832​
59​	57	163,548​	286,494,035​	188,608,483​	28,017​
60​	58	186,965​	295,240,787​	181,270,644​	23,822​
61​	59	213,440​	304,037,893​	174,088,171​	20,228​
62​	60	243,139​	312,869,486​	167,061,151​	17,109​
63​	61	276,592​	321,729,614​	160,197,084​	14,454​
64​	62	314,010​	330,601,552​	153,495,293​	12,156​
65​	63	356,006​	339,478,833​	146,962,214​	10,213​
66​	64	402,843​	348,343,999​	140,596,460​	8,539​
67​	65	455,238​	357,190,143​	134,403,445​	7,132​
68​	66	513,502​	365,999,154​	128,381,075​	5,924​
69​	67	578,466​	374,763,816​	122,533,814​	4,918​
70​	68	650,513​	383,465,483​	116,858,927​	4,056​
71​	69	730,590​	392,096,721​	111,359,943​	3,345​
72​	70	819,153​	400,638,485​	106,033,550​	2,738​
73​	71	917,295​	409,083,245​	100,882,424​	2,241​
74​	72	1,025,546​	417,411,688​	95,902,709​	1,819​
75​	73	1,145,153​	425,616,328​	91,096,300​	1,478​
76​	74	1,276,741​	433,677,709​	86,458,870​	1,188​
77​	75	1,421,717​	441,588,517​	81,991,577​	957​
78​	76	1,580,807​	449,329,331​	77,689,707​	762​
79​	77	1,755,607​	456,893,130​	73,553,760​	608​
80​	78	1,946,940​	464,260,655​	69,578,665​	478​
81​	79	2,156,600​	471,425,344​	65,764,357​	378​
82​	80	2,385,536​	478,368,257​	62,105,494​	293​
83​	81	2,635,742​	485,083,406​	58,601,481​	229​
84​	82	2,908,290​	491,552,326​	55,246,778​	175​
85​	83	3,205,408​	497,769,746​	52,040,353​	135​
86​	84	3,528,289​	503,717,820​	48,976,506​	101​
87​	85	3,879,400​	509,392,127​	46,053,843​	77​
88​	86	4,260,075​	514,775,571​	43,266,574​	56​
89​	87	4,673,020​	519,864,702​	40,612,987​	42​
90​	88	5,119,712​	524,643,328​	38,087,277​	30​
91​	89	5,603,124​	529,109,074​	35,687,484​	22​
92​	90	6,124,858​	533,246,758​	33,407,807​	15​
93​	91	6,688,158​	537,055,199​	31,246,116​	11​
94​	92	7,294,777​	540,520,348​	29,196,682​	7​
95​	93	7,948,218​	543,642,289​	27,257,222​	5​
96​	94	8,650,368​	546,408,202​	25,422,131​	3​
97​	95	9,405,017​	548,819,521​	23,689,046​	2​
98​	96	10,214,170​	550,864,741​	22,052,498​	1​
99​	97	11,081,891​	552,546,702​	20,510,093​	1​
100​	98	12,010,311​	553,855,273​	19,056,543​
101​	99	13,003,753​	554,794,743​	17,689,441​