Get summary of combinations could be made with each sum?

Kishan · Aug 21, 2017

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

Combinations

5

GR-1

1

4

7

10

13

16

19

22

25

28

31

34

37

40

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 · Aug 23, 2017

shg said:
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 · Aug 23, 2017

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	555,356,419
01		15,197,009	555,546,058
02		16,403,722	555,356,419
03		17,689,441	554,794,743
04		19,056,543	553,855,273
05	1	20,510,093	552,546,702
06	1	22,052,498	550,864,741
07	2	23,689,046	548,819,521
08	3	25,422,131	546,408,202
09	5	27,257,222	543,642,289
10	7	29,196,682	540,520,348
11	11	31,246,116	537,055,199
12	15	33,407,807	533,246,758
13	22	35,687,484	529,109,074
14	30	38,087,277	524,643,328
15	42	40,612,987	519,864,702
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	464,260,655	69,578,665
79	2,156,600	471,425,344	65,764,357
80	2,385,536	478,368,257	62,105,494
81	2,635,742	485,083,406	58,601,481
82	2,908,290	491,552,326	55,246,778
83	3,205,408	497,769,746	52,040,353
84	3,528,289	503,717,820	48,976,506
85	3,879,400	509,392,127	46,053,843
86	4,260,075	514,775,571	43,266,574
87	4,673,020	519,864,702	40,612,987
88	5,119,712	524,643,328	38,087,277
89	5,603,124	529,109,074	35,687,484
90	6,124,858	533,246,758	33,407,807
91	6,688,158	537,055,199	31,246,116
92	7,294,777	540,520,348	29,196,682
93	7,948,218	543,642,289	27,257,222
94	8,650,368	546,408,202	25,422,131
95	9,405,017	548,819,521	23,689,046
96	10,214,170	550,864,741	22,052,498
97	11,081,891	552,546,702	20,510,093
98	12,010,311	553,855,273	19,056,543
99	13,003,753	554,794,743	17,689,441

What is it you want now?

Kishan · Aug 23, 2017

shg said:
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 · Aug 23, 2017

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

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

Kishan · Aug 23, 2017

shg said:
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 · Aug 23, 2017

	G	H
6	Value wanted	# of ways
7	287	1
8	288	14
9	289	105
10	290	546
11	291	2184
12	292	7098
13	293	19383
14	294	45474
15	295	93093
16	296	168168
17	297	270270
18	298	388752
19	299	502593
20	300	585690
21	301	616227
22	302	585690
23	303	502593
24	304	388752
25	305	270270
26	306	168168
27	307	93093
28	308	45474
29	309	19383
30	310	7098
31	311	2184
32	312	546
33	313	105
34	314	14
35	315	1

Sheet3

Kishan · Aug 23, 2017

Eric W said:
G
H

6
Value wanted
# of ways

7
287
1

8
288
14

9
289
105

10
290
546

11
291
2184

12
292
7098

13
293
19383

14
294
45474

15
295
93093

16
296
168168

17
297
270270

18
298
388752

19
299
502593

20
300
585690

21
301
616227

22
302
585690

23
303
502593

24
304
388752

25
305
270270

26
306
168168

27
307
93093

28
308
45474

29
309
19383

30
310
7098

31
311
2184

32
312
546

33
313
105

34
314
14

35
315
1

<tbody>
</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 · Aug 23, 2017

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.

Code:

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 · Aug 23, 2017

Eric W said:
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

Get summary of combinations could be made with each sum?

Kishan

Well-known Member

Kishan

Well-known Member

Excel Facts

shg

MrExcel MVP

Kishan

Well-known Member

shg

MrExcel MVP

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Forum statistics

Share this page

Get summary of combinations could be made with each sum?

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock