Select cells with highest sum, given a cost constraint (ncaa bracket)

[zeiss74]

This is a bit of a rush order, since it's for an NCAA pool due tomorrow.

i have sorted my teams by odds of winning, but different seeds cost me differently.

So I have 3 columns: team-odds-cost

and I would like Excel to tell me:
"If cost can't go over 100, these are the N teams to choose to maximize the sum of odds." I would not specify N, Excel would be open to any N, as long as it adds to the highest odds.

Any chance of getting this to work tonight?

 

[zeiss74]

Nevermind, I got it. Solver was easier to learn and more robust than I expected.

 

[Eric W]

You could let Solver have a whack at it.

If you don't have it installed, click on File > Options > Add-ins > on the drop-down box on the bottom select > Excel Add-ins > Go > check Solver Add-in > OK.

Then set up your sheet like this:

	A	B	C	D	E	F	G
1	Team	odds	cost	Include	cost	sum of odds	N
2	A	0.743597	2	1	100	13.30195	20
3	B	0.322926	15	0
4	C	0.941047	8	1
5	D	0.087948	19	0
6	E	0.890444	19	0
7	F	0.09731	4	1
8	G	0.447467	11	0
9	H	0.624016	5	1
10	I	0.929633	4	1
11	J	0.551733	6	1
12	K	0.15749	14	0
13	L	0.766609	8	1
14	M	0.258603	13	0
15	N	0.125976	7	0
16	O	0.285883	11	0
17	P	0.864462	17	0
18	Q	0.967666	10	0
19	R	0.524834	14	0
20	S	0.407858	19	0
21	T	0.671374	7	1
22	U	0.01356	5	0
23	V	0.823129	17	0
24	W	0.61969	12	0
25	X	0.465948	11	0
26	Y	0.940748	10	1
27	Z	0.395033	7	0
28	AA	0.359005	12	0
29	AB	0.977296	6	1
30	AC	0.681336	19	0
31	AD	0.313026	18	0
32	AE	0.168162	9	0
33	AF	0.300937	11	0
34	AG	0.453067	5	1
35	AH	0.733698	11	0
36	AI	0.643624	15	0
37	AJ	0.504665	18	0
38	AK	0.417693	1	1
39	AL	0.73889	5	1
40	AM	0.03392	12	0
41	AN	0.509899	18	0
42	AO	0.32488	5	1
43	AP	0.111904	12	0
44	AQ	0.745815	19	0
45	AR	0.930339	11	1
46	AS	0.071492	4	0
47	AT	0.898091	15	0
48	AU	0.414297	19	0
49	AV	0.996209	17	0
50	AW	0.341761	20	0
51	AX	0.24572	9	0
52	AY	0.095352	12	0
53	AZ	0.55657	1	1
54	BA	0.704787	6	1
55	BB	0.827956	15	0
56	BC	0.020511	15	0
57	BD	0.731362	1	1
58	BE	0.343529	8	0
59	BF	0.125457	6	0
60	BG	0.438494	17	0
61	BH	0.036942	11	0
62	BI	0.261792	19	0
63	BJ	0.700999	2	1
64	BK	0.861697	14	0
65	BL	0.5	3	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula E2 =SUMPRODUCT(C2:C65,D2:D65) F2 =SUMPRODUCT(B2:B65,D2:D65) G2 =SUM(D:D) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Then go to the Data tab, click Solver (far right), and enter these parameters:

Set Objective: F2

By Changing Variable Cells: D2:D65

Subject to the Contraints:
D2:D65 = binary
E2<=100

Select a Solving Method: Evolutionary

And click Solve.

Good luck! And if you win a billion dollar prize, I think I should get a percentage!

 

[zeiss74]

Thanks Eric. You taught me a few new things: using "binary" instead of "integer and <=1." Also, sumproduct in one cell is a cleaner than me producting in a separate column and then summing. Thanks again!

 

[Eric W]

Glad to help! Good luck!

 

[zeiss74]

Glad to help! Good luck!

Well, I won Eric!! And certainly the algorithm helped (although so did Villanova's 3 point shooting).

So you can have 50% of my $0 winnings!