Help determining a best "lineup"

[Sarah]

I need to determine the best possible (statistically speaking) lineup to start.

Each car has a value, and each car has odds.

I must choose 5 cars, and the value cannot exceed 100.
I typed in all my data, can Excel choose the best possible lineup for me?

In the end, I need the lowest result possible while only using 100 pts (or
less) in
value.

Here is a sample of my data.
Car # Value Odds (#to1) (Result=Value x Odds)
1 23.5 4 94.00
2 23.4 5 117.00
3 22.8 5.75 131.10
4 22.6 6.75 152.55
5 22.2 12.5 277.50
6 23 15 345.00
7 15 23.5 352.50
8 22 17.5 385.00
9 21.8 20 436.00
10 19.5 23.5 458.25
11 21.6 23.5 507.60
12 21 25 525.00
13 21.7 25 542.50
14 19 30 570.00
15 22.1 30 663.00
16 20.3 35 710.50
17 21.9 34 744.60
18 20 40 800.00
19 18.8 45 846.00
20 18.6 47.5 883.50


Thanks for your help.

 

[Max]

Just some thoughts on this tough challenge <g> ...

Perhaps a relatively simple simulation play (minimization) using standard
worksheet functions & Excel's powerful (but seldom discussed) Data>Table
feature would be of some help here ..

A sample construct is available at:
http://www.savefile.com/files/492539
Simulation play using Data Table for optimal lineup_Minimization.xls

Source data as posted assumed within A121

In F1: =RAND()
In G1: =INDEX($A$2:$A$21,RANK(F2,$F$2:$F$21))
In H1: =INDEX(B:B,MATCH($G2,$A:$A,0))
In I1: =INDEX(D,MATCH($G2,$A:$A,0))
Select F1:I1, copy down to I21

In J2: =IF(SUM(H2:H6)>100,"",SUM(H2:H6))
In K2: =IF(J2="","",SUM(I2:I6))
In L2: =IF(J2="","",G2&","&G3&","&G4&","&G5&","&G6)
Select J2:L2, copy down to L17

The above sets up the base part of it which randomly picks 16 combos of 5
cars from the pool of 20, "filters" the 16 combos to satisfy the criteria
(Sum of "Values" < 100), and then return the "Sum of Results" & concats of
combos which satisfies the criteria.

Then to extract the single "min" result from the base set-up:
In M2: =MIN(K:K)
In M3: =IF(M2=0,"",INDEX(L:L,MATCH(M2,K:K,0)))

Now to set it up to simulate/iterate say: 1000 runs at one go
using the Data > Table feature

In P2: =M3
In Q2: =M2
In O2:O1001, list the numbers 1,2,3,... 1000

Select O1:Q1001, click Data > Table
In the dialog, leave "Row input cell" empty, enter in
Column input cell: R2 (say*)
Click OK
*can be any cell outside O1:Q1001

The above creates 2 "one variable" data tables for simulating at one go,
1001 random minimization** runs/picks of 5-cars satisfying the criteria (Sum
of "Values" < 100)
**Minimum of the "Sum of Results"

To extract/monitor the "min-of-min" results from the data tables in any one
run:

In S1, array-entered (press CTRL+SHIFT+ENTER):
=MIN(IF(Q1:Q1001>0,Q1:Q1001))

In T1: =IF(S1=0,"",INDEX(P,MATCH(S1,Q:Q,0)))

Then to play with the model, just press F9 several times to regenerate. Keep
your eye on the "min" values returned in S1 (T1 returns the source combo pick
of the 5 cars). Maybe copy and paste special as values elsewhere some of the
S1:T1 results which seem low enough.

Playing with it here reveals a possible best combo pick of cars #:
1,3,7,10,14 satisfying the criteria (Sum of "Values" < 100) with a min "Sum
of Results" of 1605.85

 

[Max]

And if you want it the other way for the objective, ie maximize "Sum of
Results" (instead of minimize), just replace the model's formulae in both M2
and S1 with "MAX", viz:

In M2: =MAX(K:K)
In S1: =MAX(Q:Q)

Playing with maximization here reveals a possible best combo pick of cars #:
16,17,18,19,20 satisfying the criteria (Sum of "Values" < 100) with a max
"Sum of Results" of 3984.6.

 

[Sarah]

Worked like a charm...Thank you so much, this is amazing!

 

[Max]

You're welcome, Sarah.
Glad it provided an option here ..