Maximum points generator chart

[selkov]

I manage a team that has a handicapped players skill level. There are 8 players on the team and we can play a maximum of 5 per week.
There is a target maximum combined skill level of 2375, if we exceed that we give up points.

Our players skill levels change sometimes weekly based on prior weeks performance so I always need to recalculate this.
I have tried to create a spreadsheet where i can type in the 8 players individual skill levels and have it show me all the possible combinations that will keep me un der the 2375 limit.
I can't do it.
Would someone be willing to take a crack at this for me?

Skill levels range form 100 to 900.

Thank you all.

 

[selkov]

So another development. or two.
One is if I need to remove a player from this weeks competition and remove him from the list [delete entry] the macro breaks.

Also i would like to run this on an android tablet but the excel app does not support macros. Any ideas?

 

[Eric W]

As far as the first development, when you delete an entry, do you delete it from the middle of the list? The macro assumes there are no gaps in the list. If you delete someone from the middle, put the last name in that row. I could update the macro if you want to handle that, but if you want something to run on a tablet, it sounds like you need a formula solution. Kevin9999 provided one in an earlier post, you could try that. Here's an alternate one I came up with:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Player	Level		Max		2345	B, D, F, G, H	00000000	0	0
2	A	100		2375		2333	B, C, E, F, H	00000001	1	695	H
3	B	250				2333	A, C, D, F, H	00000010	1	750	G
4	C	888		# of players		2295	A, D, E, G, H	00000011	2	1445	G, H
5	D	450		8		2288	A, B, C, E, G	00000100	1	200	F
6	E	300				2245	A, B, D, G, H	00000101	2	895	F, H
7	F	200				2238	A, C, E, F, G	00000110	2	950	F, G
8	G	750				2233	A, B, C, E, H	00000111	3	1645	F, G, H
9	H	695				2195	B, E, F, G, H	00001000	1	300	E
10						2195	A, D, F, G, H	00001001	2	995	E, H
11						2188	A, B, C, F, G	00001010	2	1050	E, G
12						2183	A, C, E, F, H	00001011	3	1745	E, G, H
13						2133	A, B, C, F, H	00001100	2	500	E, F
14						2095	A, B, E, G, H	00001101	3	1195	E, F, H
15						2088	B, C, D, E, F	00001110	3	1250	E, F, G
16						2045	A, E, F, G, H	00001111	4	1945	E, F, G, H
17						1995	A, B, F, G, H	00010000	1	450	D
18						1988	A, B, C, D, E	00010001	2	1145	D, H
19						1950	B, D, E, F, G	00010010	2	1200	D, G
20						1938	A, C, D, E, F	00010011	3	1895	D, G, H
21						1895	B, D, E, F, H	00010100	2	650	D, F
22						1888	A, B, C, D, F	00010101	3	1345	D, F, H
23						1850	A, B, D, E, G	00010110	3	1400	D, F, G
24						1800	A, D, E, F, G	00010111	4	2095	D, F, G, H
25						1795	A, B, D, E, H	00011000	2	750	D, E
26						1750	A, B, D, F, G	00011001	3	1445	D, E, H
27						1745	A, D, E, F, H	00011010	3	1500	D, E, G
28						1738	A, B, C, E, F	00011011	4	2195	D, E, G, H
29						1695	A, B, D, F, H	00011100	3	950	D, E, F
30						1600	A, B, E, F, G	00011101	4	1645	D, E, F, H
31						1545	A, B, E, F, H	00011110	4	1700	D, E, F, G
32						1300	A, B, D, E, F	00011111	5	2395	D, E, F, G, H
33								00100000	1	888	C
34								00100001	2	1583	C, H
35								00100010	2	1638	C, G
Sheet2

Cell Formulas
Range	Formula
F1:G32	F1	=LET(c,I1#,t,J1#,n,OFFSET(K1,0,0,2^D5),g,CHOOSE({1,2},t,n),SORT(FILTER(g,(c=5)*(t<=D2)),1,-1))
H1:H256	H1	=DEC2BIN(SEQUENCE(2^D5,,0),D5)
I1:I256	I1	=LEN(SUBSTITUTE(H1#,"0",""))
J1:J256	J1	=MMULT(MID(H1#,SEQUENCE(,D5),1)+0,OFFSET(B2,0,0,D5))
D5	D5	=COUNTA(A2:A9)
K1:K35	K1	=TEXTJOIN(", ",1,IF(MID(H1,SEQUENCE($D$5),1)="1",OFFSET($A$2,0,0,$D$5),""))
Dynamic array formulas.

This uses some of the newer array functions. The H:K columns are basically helper columns. You only need to enter the H:J formulas in row 1, and they should fill the rest of the way. The K formula I couldn't do with a Spill formula, so you have to drag it down, although I believe it could be done with the BYROW function (which I don't have). Then the F1 function filters out unwanted rows and sorts by total level. Depending on what version of Excel you have, these could be tweaked.

In any event, you have a few options to look at. Let us know.

 

[Coyotex3]

As far as the first development, when you delete an entry, do you delete it from the middle of the list? The macro assumes there are no gaps in the list. If you delete someone from the middle, put the last name in that row. I could update the macro if you want to handle that, but if you want something to run on a tablet, it sounds like you need a formula solution. Kevin9999 provided one in an earlier post, you could try that. Here's an alternate one I came up with:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Player	Level		Max		2345	B, D, F, G, H	00000000	0	0
2	A	100		2375		2333	B, C, E, F, H	00000001	1	695	H
3	B	250				2333	A, C, D, F, H	00000010	1	750	G
4	C	888		# of players		2295	A, D, E, G, H	00000011	2	1445	G, H
5	D	450		8		2288	A, B, C, E, G	00000100	1	200	F
6	E	300				2245	A, B, D, G, H	00000101	2	895	F, H
7	F	200				2238	A, C, E, F, G	00000110	2	950	F, G
8	G	750				2233	A, B, C, E, H	00000111	3	1645	F, G, H
9	H	695				2195	B, E, F, G, H	00001000	1	300	E
10						2195	A, D, F, G, H	00001001	2	995	E, H
11						2188	A, B, C, F, G	00001010	2	1050	E, G
12						2183	A, C, E, F, H	00001011	3	1745	E, G, H
13						2133	A, B, C, F, H	00001100	2	500	E, F
14						2095	A, B, E, G, H	00001101	3	1195	E, F, H
15						2088	B, C, D, E, F	00001110	3	1250	E, F, G
16						2045	A, E, F, G, H	00001111	4	1945	E, F, G, H
17						1995	A, B, F, G, H	00010000	1	450	D
18						1988	A, B, C, D, E	00010001	2	1145	D, H
19						1950	B, D, E, F, G	00010010	2	1200	D, G
20						1938	A, C, D, E, F	00010011	3	1895	D, G, H
21						1895	B, D, E, F, H	00010100	2	650	D, F
22						1888	A, B, C, D, F	00010101	3	1345	D, F, H
23						1850	A, B, D, E, G	00010110	3	1400	D, F, G
24						1800	A, D, E, F, G	00010111	4	2095	D, F, G, H
25						1795	A, B, D, E, H	00011000	2	750	D, E
26						1750	A, B, D, F, G	00011001	3	1445	D, E, H
27						1745	A, D, E, F, H	00011010	3	1500	D, E, G
28						1738	A, B, C, E, F	00011011	4	2195	D, E, G, H
29						1695	A, B, D, F, H	00011100	3	950	D, E, F
30						1600	A, B, E, F, G	00011101	4	1645	D, E, F, H
31						1545	A, B, E, F, H	00011110	4	1700	D, E, F, G
32						1300	A, B, D, E, F	00011111	5	2395	D, E, F, G, H
33								00100000	1	888	C
34								00100001	2	1583	C, H
35								00100010	2	1638	C, G
Sheet2

Cell Formulas
Range	Formula
F1:G32	F1	=LET(c,I1#,t,J1#,n,OFFSET(K1,0,0,2^D5),g,CHOOSE({1,2},t,n),SORT(FILTER(g,(c=5)*(t<=D2)),1,-1))
H1:H256	H1	=DEC2BIN(SEQUENCE(2^D5,,0),D5)
I1:I256	I1	=LEN(SUBSTITUTE(H1#,"0",""))
J1:J256	J1	=MMULT(MID(H1#,SEQUENCE(,D5),1)+0,OFFSET(B2,0,0,D5))
D5	D5	=COUNTA(A2:A9)
K1:K35	K1	=TEXTJOIN(", ",1,IF(MID(H1,SEQUENCE($D$5),1)="1",OFFSET($A$2,0,0,$D$5),""))
Dynamic array formulas.

This uses some of the newer array functions. The H:K columns are basically helper columns. You only need to enter the H:J formulas in row 1, and they should fill the rest of the way. The K formula I couldn't do with a Spill formula, so you have to drag it down, although I believe it could be done with the BYROW function (which I don't have). Then the F1 function filters out unwanted rows and sorts by total level. Depending on what version of Excel you have, these could be tweaked.

In any event, you have a few options to look at. Let us know.

How does one even think of such a formula !

 

[Eric W]

How does one even think of such a formula !

A love of math, decades of programming experience, and lots of time playing with Excel!