cant figure this out..

[AWilson]

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?

 

[lhartono]

330 combinations equal sum of 23.

 

[Eric W]

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?

 

[AWilson]

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!!

 

[Eric W]

So in this case, the 4s and 6s must be considered as unique, since they refer to different people. In that case, try:

	A	B	C	D	E	F	G	H	I
1	7		00000002	1	2		Row	Combination	Sum
2	6		00000030	1	3		31	00054432	18
3	6		00000032	2	5		47	00604432	19
4	5		00000400	1	4		55	00650432	20
5	4		00000402	2	6		59	00654032	20
6	4		00000430	2	7		61	00654402	21
7	3		00000432	3	9		62	00654430	22
8	2		00004000	1	4		79	06004432	19
9			00004002	2	6		87	06050432	20
10			00004030	2	7		91	06054032	20
11			00004032	3	9		93	06054402	21
12			00004400	2	8		94	06054430	22
13			00004402	3	10		103	06600432	21
14			00004430	3	11		107	06604032	21
15			00004432	4	13		109	06604402	22
16			00050000	1	5		110	06604430	23
17			00050002	2	7		115	06650032	22
18			00050030	2	8		117	06650402	23
19			00050032	3	10		121	06654002	23
20			00050400	2	9		143	70004432	20
21			00050402	3	11		151	70050432	21
22			00050430	3	12		155	70054032	21
23			00050432	4	14		157	70054402	22
24			00054000	2	9		158	70054430	23
25			00054002	3	11		167	70600432	22
26			00054030	3	12		171	70604032	22
27			00054032	4	14		173	70604402	23
28			00054400	3	13		179	70650032	23
29			00054402	4	15		199	76000432	22
30			00054430	4	16		203	76004032	22
31			00054432	5	18		205	76004402	23
32			00600000	1	6		211	76050032	23
33			00600002	2	8
34			00600030	2	9

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =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") D1 =LEN(SUBSTITUTE(C1,"0","")) E1 =SUM(MID(C1,{1,2,3,4,5,6,7,8},1)+0) H2 =IF(G2="","",INDEX(C:C,G2)) I2 =IF(G2="","",INDEX(E:E,G2)) <tbody> </tbody>

<tbody>
</tbody>

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

<tbody>
</tbody>

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!

 

[AWilson]

AWESOME!! Thank you so much!! You Rock!

 

[Eric W]

Glad to help!