JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Get summary of combinations could be made with each sum?
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
Thank you in advance
Regards,
Kishan
Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
Regards,
Kishan
Hi,
The answers are in the hundreds of millions, Kishan. What would you do with them?
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
Last edited: Aug 22, 2017
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
Hi,individually from sum 287 to 315
Here is the histogram of totals.
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]A
[/td][td="bgcolor:#C0C0C0"]B
[/td][td="bgcolor:#C0C0C0"]C
[/td][td="bgcolor:#C0C0C0"]D
[/td][td="bgcolor:#C0C0C0"]E
[/td][/tr][tr][td="bgcolor:#C0C0C0"]1
[/td][td][/td][td]100
[/td][td]200
[/td][td]300
[/td][td]400
[/td][/tr]2
[/td][td]00 14,064,454
[/td][td]555,356,419
[/td][td]16,403,722
[/td][/tr]3
[/td][td]01 15,197,009
[/td][td]555,546,058
[/td][td]15,197,009
[/td][/tr]4
[/td][td]02 16,403,722
[/td][td]555,356,419
[/td][td]14,064,454
[/td][/tr]5
[/td][td]03 17,689,441
[/td][td]554,794,743
[/td][td]13,003,753
[/td][/tr]6
[/td][td]04 19,056,543
[/td][td]553,855,273
[/td][td]12,010,311
[/td][/tr]7
[/td][td]05 1
[/td][td]20,510,093
[/td][td]552,546,702
[/td][td]11,081,891
[/td][/tr]8
[/td][td]06 1
[/td][td]22,052,498
[/td][td]550,864,741
[/td][td]10,214,170
[/td][/tr]9
[/td][td]07 2
[/td][td]23,689,046
[/td][td]548,819,521
[/td][td]9,405,017
[/td][/tr]10
[/td][td]08 3
[/td][td]25,422,131
[/td][td]546,408,202
[/td][td]8,650,368
[/td][/tr]11
[/td][td]09 5
[/td][td]27,257,222
[/td][td]543,642,289
[/td][td]7,948,218
[/td][/tr]12
[/td][td]10 7
[/td][td]29,196,682
[/td][td]540,520,348
[/td][td]7,294,777
[/td][/tr]13
[/td][td]11 11
[/td][td]31,246,116
[/td][td]537,055,199
[/td][td]6,688,158
[/td][/tr]14
[/td][td]12 15
[/td][td]33,407,807
[/td][td]533,246,758
[/td][td]6,124,858
[/td][/tr]15
[/td][td]13 22
[/td][td]35,687,484
[/td][td]529,109,074
[/td][td]5,603,124
[/td][/tr]16
[/td][td]14 30
[/td][td]38,087,277
[/td][td]524,643,328
[/td][td]5,119,712
[/td][/tr]17
[/td][td]15 42
[/td][td]40,612,987
[/td][td]519,864,702
[/td][td]4,673,020
[/td][/tr]18
[/td][td]16 56
[/td][td]43,266,574
[/td][td]514,775,571
[/td][td]4,260,075
[/td][/tr]19
[/td][td]17 77
[/td][td]46,053,843
[/td][td]509,392,127
[/td][td]3,879,400
[/td][/tr]20
[/td][td]18 101
[/td][td]48,976,506
[/td][td]503,717,820
[/td][td]3,528,289
[/td][/tr]21
[/td][td]19 135
[/td][td]52,040,353
[/td][td]497,769,746
[/td][td]3,205,408
[/td][/tr]22
[/td][td]20 175
[/td][td]55,246,778
[/td][td]491,552,326
[/td][td]2,908,290
[/td][/tr]23
[/td][td]21 229
[/td][td]58,601,481
[/td][td]485,083,406
[/td][td]2,635,742
[/td][/tr]24
[/td][td]22 293
[/td][td]62,105,494
[/td][td]478,368,257
[/td][td]2,385,536
[/td][/tr]25
[/td][td]23 378
[/td][td]65,764,357
[/td][td]471,425,344
[/td][td]2,156,600
[/td][/tr]26
[/td][td]24 478
[/td][td]69,578,665
[/td][td]464,260,655
[/td][td]1,946,940
[/td][/tr]27
[/td][td]25 608
[/td][td]73,553,760
[/td][td]456,893,130
[/td][td]1,755,607
[/td][/tr]28
[/td][td]26 762
[/td][td]77,689,707
[/td][td]449,329,331
[/td][td]1,580,807
[/td][/tr]29
[/td][td]27 957
[/td][td]81,991,577
[/td][td]441,588,517
[/td][td]1,421,717
[/td][/tr]30
[/td][td]28 1,188
[/td][td]86,458,870
[/td][td]433,677,709
[/td][td]1,276,741
[/td][/tr]31
[/td][td]29 1,478
[/td][td]91,096,300
[/td][td]425,616,328
[/td][td]1,145,153
[/td][/tr]32
[/td][td]30 1,819
[/td][td]95,902,709
[/td][td]417,411,688
[/td][td]1,025,546
[/td][/tr]33
[/td][td]31 2,241
[/td][td]100,882,424
[/td][td]409,083,245
[/td][td]917,295
[/td][/tr]34
[/td][td]32 2,738
[/td][td]106,033,550
[/td][td]400,638,485
[/td][td]819,153
[/td][/tr]35
[/td][td]33 3,345
[/td][td]111,359,943
[/td][td]392,096,721
[/td][td]730,590
[/td][/tr]36
[/td][td]34 4,056
[/td][td]116,858,927
[/td][td]383,465,483
[/td][td]650,513
[/td][/tr]37
[/td][td]35 4,918
[/td][td]122,533,814
[/td][td]374,763,816
[/td][td]578,466
[/td][/tr]38
[/td][td]36 5,924
[/td][td]128,381,075
[/td][td]365,999,154
[/td][td]513,502
[/td][/tr]39
[/td][td]37 7,132
[/td][td]134,403,445
[/td][td]357,190,143
[/td][td]455,238
[/td][/tr]40
[/td][td]38 8,539
[/td][td]140,596,460
[/td][td]348,343,999
[/td][td]402,843
[/td][/tr]41
[/td][td]39 10,213
[/td][td]146,962,214
[/td][td]339,478,833
[/td][td]356,006
[/td][/tr]42
[/td][td]40 12,156
[/td][td]153,495,293
[/td][td]330,601,552
[/td][td]314,010
[/td][/tr]43
[/td][td]41 14,454
[/td][td]160,197,084
[/td][td]321,729,614
[/td][td]276,592
[/td][/tr]44
[/td][td]42 17,109
[/td][td]167,061,151
[/td][td]312,869,486
[/td][td]243,139
[/td][/tr]45
[/td][td]43 20,228
[/td][td]174,088,171
[/td][td]304,037,893
[/td][td]213,440
[/td][/tr]46
[/td][td]44 23,822
[/td][td]181,270,644
[/td][td]295,240,787
[/td][td]186,965
[/td][/tr]47
[/td][td]45 28,017
[/td][td]188,608,483
[/td][td]286,494,035
[/td][td]163,548
[/td][/tr]48
[/td][td]46 32,832
[/td][td]196,093,117
[/td][td]277,803,005
[/td][td]142,738
[/td][/tr]49
[/td][td]47 38,424
[/td][td]203,723,667
[/td][td]269,182,646
[/td][td]124,400
[/td][/tr]50
[/td][td]48 44,819
[/td][td]211,490,470
[/td][td]260,637,657
[/td][td]108,156
[/td][/tr]51
[/td][td]49 52,207
[/td][td]219,391,864
[/td][td]252,182,012
[/td][td]93,901
[/td][/tr]52
[/td][td]50 60,630
[/td][td]227,417,067
[/td][td]243,819,692
[/td][td]81,311
[/td][/tr]53
[/td][td]51 70,311
[/td][td]235,563,628
[/td][td]235,563,628
[/td][td]70,311
[/td][/tr]54
[/td][td]52 81,311
[/td][td]243,819,692
[/td][td]227,417,067
[/td][td]60,630
[/td][/tr]55
[/td][td]53 93,901
[/td][td]252,182,012
[/td][td]219,391,864
[/td][td]52,207
[/td][/tr]56
[/td][td]54 108,156
[/td][td]260,637,657
[/td][td]211,490,470
[/td][td]44,819
[/td][/tr]57
[/td][td]55 124,400
[/td][td]269,182,646
[/td][td]203,723,667
[/td][td]38,424
[/td][/tr]58
[/td][td]56 142,738
[/td][td]277,803,005
[/td][td]196,093,117
[/td][td]32,832
[/td][/tr]59
[/td][td]57 163,548
[/td][td]286,494,035
[/td][td]188,608,483
[/td][td]28,017
[/td][/tr]60
[/td][td]58 186,965
[/td][td]295,240,787
[/td][td]181,270,644
[/td][td]23,822
[/td][/tr]61
[/td][td]59 213,440
[/td][td]304,037,893
[/td][td]174,088,171
[/td][td]20,228
[/td][/tr]62
[/td][td]60 243,139
[/td][td]312,869,486
[/td][td]167,061,151
[/td][td]17,109
[/td][/tr]63
[/td][td]61 276,592
[/td][td]321,729,614
[/td][td]160,197,084
[/td][td]14,454
[/td][/tr]64
[/td][td]62 314,010
[/td][td]330,601,552
[/td][td]153,495,293
[/td][td]12,156
[/td][/tr]65
[/td][td]63 356,006
[/td][td]339,478,833
[/td][td]146,962,214
[/td][td]10,213
[/td][/tr]66
[/td][td]64 402,843
[/td][td]348,343,999
[/td][td]140,596,460
[/td][td]8,539
[/td][/tr]67
[/td][td]65 455,238
[/td][td]357,190,143
[/td][td]134,403,445
[/td][td]7,132
[/td][/tr]68
[/td][td]66 513,502
[/td][td]365,999,154
[/td][td]128,381,075
[/td][td]5,924
[/td][/tr]69
[/td][td]67 578,466
[/td][td]374,763,816
[/td][td]122,533,814
[/td][td]4,918
[/td][/tr]70
[/td][td]68 650,513
[/td][td]383,465,483
[/td][td]116,858,927
[/td][td]4,056
[/td][/tr]71
[/td][td]69 730,590
[/td][td]392,096,721
[/td][td]111,359,943
[/td][td]3,345
[/td][/tr]72
[/td][td]70 819,153
[/td][td]400,638,485
[/td][td]106,033,550
[/td][td]2,738
[/td][/tr]73
[/td][td]71 917,295
[/td][td]409,083,245
[/td][td]100,882,424
[/td][td]2,241
[/td][/tr]74
[/td][td]72 1,025,546
[/td][td]417,411,688
[/td][td]95,902,709
[/td][td]1,819
[/td][/tr]75
[/td][td]73 1,145,153
[/td][td]425,616,328
[/td][td]91,096,300
[/td][td]1,478
[/td][/tr]76
[/td][td]74 1,276,741
[/td][td]433,677,709
[/td][td]86,458,870
[/td][td]1,188
[/td][/tr]77
[/td][td]75 1,421,717
[/td][td]441,588,517
[/td][td]81,991,577
[/td][td]957
[/td][/tr]78
[/td][td]76 1,580,807
[/td][td]449,329,331
[/td][td]77,689,707
[/td][td]762
[/td][/tr]79
[/td][td]77 1,755,607
[/td][td]456,893,130
[/td][td]73,553,760
[/td][td]608
[/td][/tr]80
[/td][td]78 1,946,940
[/td][td]464,260,655
[/td][td]69,578,665
[/td][td]478
[/td][/tr]81
[/td][td]79 2,156,600
[/td][td]471,425,344
[/td][td]65,764,357
[/td][td]378
[/td][/tr]82
[/td][td]80 2,385,536
[/td][td]478,368,257
[/td][td]62,105,494
[/td][td]293
[/td][/tr]83
[/td][td]81 2,635,742
[/td][td]485,083,406
[/td][td]58,601,481
[/td][td]229
[/td][/tr]84
[/td][td]82 2,908,290
[/td][td]491,552,326
[/td][td]55,246,778
[/td][td]175
[/td][/tr]85
[/td][td]83 3,205,408
[/td][td]497,769,746
[/td][td]52,040,353
[/td][td]135
[/td][/tr]86
[/td][td]84 3,528,289
[/td][td]503,717,820
[/td][td]48,976,506
[/td][td]101
[/td][/tr]87
[/td][td]85 3,879,400
[/td][td]509,392,127
[/td][td]46,053,843
[/td][td]77
[/td][/tr]88
[/td][td]86 4,260,075
[/td][td]514,775,571
[/td][td]43,266,574
[/td][td]56
[/td][/tr]89
[/td][td]87 4,673,020
[/td][td]519,864,702
[/td][td]40,612,987
[/td][td]42
[/td][/tr]90
[/td][td]88 5,119,712
[/td][td]524,643,328
[/td][td]38,087,277
[/td][td]30
[/td][/tr]91
[/td][td]89 5,603,124
[/td][td]529,109,074
[/td][td]35,687,484
[/td][td]22
[/td][/tr]92
[/td][td]90 6,124,858
[/td][td]533,246,758
[/td][td]33,407,807
[/td][td]15
[/td][/tr]93
[/td][td]91 6,688,158
[/td][td]537,055,199
[/td][td]31,246,116
[/td][td]11
[/td][/tr]94
[/td][td]92 7,294,777
[/td][td]540,520,348
[/td][td]29,196,682
[/td][td]7
[/td][/tr]95
[/td][td]93 7,948,218
[/td][td]543,642,289
[/td][td]27,257,222
[/td][td]5
[/td][/tr]96
[/td][td]94 8,650,368
[/td][td]546,408,202
[/td][td]25,422,131
[/td][td]3
[/td][/tr]97
[/td][td]95 9,405,017
[/td][td]548,819,521
[/td][td]23,689,046
[/td][td]2
[/td][/tr]98
[/td][td]96 10,214,170
[/td][td]550,864,741
[/td][td]22,052,498
[/td][td]1
[/td][/tr]99
[/td][td]97 11,081,891
[/td][td]552,546,702
[/td][td]20,510,093
[/td][td]1
[/td][/tr]100
[/td][td]98 12,010,311
[/td][td]553,855,273
[/td][td]19,056,543
[/td][td="bgcolor:#D9D9D9"][/td][/tr]101
[/td][td]99 13,003,753
[/td][td]554,794,743
[/td][td]17,689,441
[/td][td="bgcolor:#D9D9D9"][/td][/tr]
Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu
