Help finding the best lineup

[Guest]

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

 

[Guest]

Select all 4 columns, use Data | Sort to sort by the Value column in
ascending order. Put either of these formulas into another column. This
assumes that the data starts in row 2 and the columns used are A, B, C, D
and this formula can go into E3 (note that it goes into row 3, not 2)
=IF(SUM(B$2:B3)>100,"Exclude","")
and fill to the end of the list.
This formula (placed in column F ?) will show the actual value of points at
each row:
=SUM(B$2:B3)
again, fill to the end of the list.

 

[Guest]

Did not work how I would want it to. That gives me the top 4 cars, and then
the lowest one. In reality, if I took 5 cars in the middle, i would get a
lower result number and still be under 100. I need it to take into account
the value (keeping it under 100) but still need it to figure out what combo
of 5 that stays under 100 gives me the lowest possible resulting number.

 

[Guest]

Confirm that in the end, nothing really matters but the entries in the Value
column - and that you must pick 5 cars (not 4 or 6, but exactly 5) that gets
you closest to 100 points without going over 100 points?

 

[Guest]

Before posting a (macro) solution, let me ask: is the answer
Cars # 1, 2, 7, 10 and 20 (total points = exactly 100)
?
If that's correct, then YES, Excel can give you the answer via a 'brute
force' code solution. Checks all 15,504 possible combinations for the 5
entries that total <= 100 and 'remembers' the one that's closest to 100.
Only provides a single solution (first found) if 2 or more combinations
result in the same answer. Leave them in the order shown (sequenced by car
#).

Sub FindBestCombination()
Dim theValues(1 To 20) As Single
Dim theBestResult(1 To 6) As Single
Dim intA, intB, IntC, IntD, IntE As Integer
Dim testValue As Single
'load data
Range("B2").Select
For intA = 1 To 20
theValues(intA) = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Next
For intA = 1 To 16
For intB = 2 To 17
For IntC = 3 To 18
For IntD = 4 To 19
For IntE = 5 To 20
testValue = theValues(intA) + _
theValues(intB) + _
theValues(IntC) + _
theValues(IntD) + _
theValues(IntE)
If testValue <= 100 Then
If (100 - testValue) < _
(100 - theBestResult(6)) Then
theBestResult(1) = intA
theBestResult(2) = intB
theBestResult(3) = IntC
theBestResult(4) = IntD
theBestResult(5) = IntE
theBestResult(6) = testValue
End If
End If
Next
Next
Next
Next
Next
Range("B23").Select
For intA = 1 To 5
ActiveCell = "Car #: " & theBestResult(intA) & _
"(" & theValues(theBestResult(intA)) & ")"
ActiveCell.Offset(1, 0).Activate
Next
ActiveCell = "Total Points: " & theBestResult(6)
End Sub