cant figure this out..

AWilson

New Member
Joined
Dec 12, 2017
Messages
3
From these eight numbers 7, 6, 6, 5, 4, 4, 3, 2. Using only 5 of the 8 numbers what combinations equal sum of 23 or less?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
AWilson, Welcome to the forum.

lhartono, I'm not sure how you arrived at that number. The number of combinations of 8 things taken 5 at a time is C(8,5) = 56. Of those 56, some will sum to over 23. I get a count of 31 combinations. And even of that 31, some of them appear to be duplicates, due to the duplicate values in the original list. If we want those eliminated too, I get only 17 combinations.

AWilson, why do you need this? Do you want formulas, VBA, or just an explanation of the math?
 
Upvote 0
Thanks Eric! I knew 330 was not correct. Actually this is what it's for.. We play on a pool league with a very important tournament this weekend. We have 8 players with the following skill levels (7, 6, 6, 5, 4, 4, 3, 2). Only 5 out of 8 play in a match. Skill level combination can not be more than 23. I was just trying to make me an easy reference sheet to use on Saturday of the different combinations of which players we can use each match. I'm ok in Excel but when I started trying to figure this out I just went into beginner mode. LOL So.. I need a sheet with the different combinations. You can tell me how to do it and I will do it myself or just however you would like! Thanks so much!!
 
Upvote 0
So in this case, the 4s and 6s must be considered as unique, since they refer to different people. In that case, try:

ABCDEFGHI
RowCombinationSum

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00000002[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00000030[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]00054432[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00000032[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]00604432[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00000400[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]00650432[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00000402[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]00654032[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00000430[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]00654402[/TD]
[TD="align: right"]21[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00000432[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]00654430[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00004000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]06004432[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00004002[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]06050432[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00004030[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]06054032[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00004032[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]06054402[/TD]
[TD="align: right"]21[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00004400[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]06054430[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00004402[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]06600432[/TD]
[TD="align: right"]21[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00004430[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]06604032[/TD]
[TD="align: right"]21[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00004432[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]06604402[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00050000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]06604430[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00050002[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]06650032[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00050030[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]06650402[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00050032[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]06654002[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00050400[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]70004432[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00050402[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]70050432[/TD]
[TD="align: right"]21[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00050430[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]70054032[/TD]
[TD="align: right"]21[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00050432[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]70054402[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00054000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]70054430[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00054002[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]70600432[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00054030[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]70604032[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00054032[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]173[/TD]
[TD="align: right"]70604402[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00054400[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]70650032[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00054402[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]199[/TD]
[TD="align: right"]76000432[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00054430[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]76004032[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00054432[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]76004402[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00600000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]76050032[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00600002[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]00600030[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=TEXT(MMULT(10^{7,6,5,4,3,2,1,0},MID(DEC2BIN(ROW(),8),{1;2;3;4;5;6;7;8},1)*$A$1:$A$8),"00000000")[/TD]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]=LEN(SUBSTITUTE(C1,"0",""))[/TD]
[/TR]
[TR]
[TH]E1[/TH]
[TD="align: left"]=SUM(MID(C1,{1,2,3,4,5,6,7,8},1)+0)[/TD]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=IF(G2="","",INDEX(C:C,G2))[/TD]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]=IF(G2="","",INDEX(E:E,G2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($D$1:$D$255=5,IF($E$1:$E$255<=23,ROW($D$1:$D$255))),ROWS($G$2:$G2)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




Put your list of players in A1:A8. Then put the C1:E1 formulas in. Now copy C1:E1, and copy them down the columns to row 255. Column C contains every combination of your 8 players, with 0 meaning "not in this combination". D is the number of players in the combination, and E is the sum of the skill levels. Now put the headings in G1:I1. Put the G2:I2 formulas in (G2 is an array formula), then copy them down to row 32. These formulas just search through the table in C1:E255 to find valid combinations.

Of course, you can just copy columns H and I - they contain the full list.

Good luck!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top