Finding Max Value Combination with Two Constraints

[LoPo87]

Hello,

I am trying to generate all combinations of sums taking 5 values from one set of numbers, and 1 value from another set of numbers, with an additional constraint. I am trying to determine the combinations of 5 "Drivers and 1 "Team" that will generate the highest sum of 'Value' with the constraint that the sum of their 'Salary' remains equal to or less than 100. Here is the data:

So my constraints are:
a.) Must use exactly 5 drivers
b.) Must use only 1 Team
c.) Salary sum of the 6 must be equal to or less than 100

Determine:
A list of all combinations such that I can sort by highest TOTAL VALUE given the 100 salary limit. It would be nice for each combination generated, to see what their respective salary sum is as well.

Any help would be appreciated!

 

[Eric W]

Welcome to the forum!

Here is the best solution I found using the Solver:

Book1
	A	B	C	D	E	F	G	H	I
1	Driver Name	Value	Salary	Use?		Team Name	Value	Salary	Use?
2	Hamilton	40.82	33.5	0		Mercedes	59.65	38	0
3	Verstappen	26.88	24.8	0		Red Bull	41.12	25.9	0
4	Bottas	25.59	23.6	1		McLaren	30.35	18.9	0
5	Ricciardo	21.41	17.3	1		Aston Martin	28.94	17.6	0
6	Perez	18.71	18.4	1		Alpine	28.12	15.4	1
7	Norris	17.71	13.1	1		Ferrari	21.53	18.1	0
8	Sainz	15.88	14.4	0		AlphaTauri	20.94	12.7	0
9	Alonso	15.41	15.6	0		Alfa Romeo	15.12	8.9	0
10	Leclerc	14	16.8	0		Williams	10.41	6.3	0
11	Gasly	12.82	11.7	1		Haas	9.59	6.1	0
12	Raikkonen	10.53	9.6	0					1
13	Stroll	10.53	13.9	0
14	Tsunoda	9.29	8.8	0
15	Ocon	8.76	10.1	0
16	Vettel	7.47	16.2	0
17	Giovinazzi	6.06	7.9	0
18	Latifi	4.71	6.5	0
19	Russell	4.53	6.2	0
20	Schumacker	4	5.8	0
21	Mazepin	2.18	5.5	0		Value	Salary
22				5		124.36	99.5
Sheet3

Cell Formulas
Range	Formula
I12	I12	=SUM(I2:I11)
D22	D22	=SUM(D2:D21)
F22	F22	=SUMPRODUCT(B2:B21,D2:D21)+SUMPRODUCT(G2:G11,I2:I11)
G22	G22	=SUMPRODUCT(C2:C21,D2:D21)+SUMPRODUCT(H2:H11,I2:I11)

The problem is that the Solver is not necessarily the best tool for this kind of problem, and the result might not actually be the best. And of course, it only gives 1 result, not the list you want. In the past I've written several macros that do what you want, only for baseball or football teams. Unfortunately, I don't have time to write one right now. Here's one that probably wouldn't be too hard to adapt if I had time:

Optimizing a Fantasy Team in Excel

Hi everyone - it's been a long time since I posted here... so long that I needed to make a new account! I am looking for some help/suggestions on how to solve this problem. I want to create an optimization for my NFL Fantasy Draft. To start this is what I would like to figure out. I have the...

www.mrexcel.com