Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help determining a best "lineup"
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help determining a best "lineup"
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) DataTable 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 A1:D21 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: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:Q10010,Q1:Q1001)) In T1: =IF(S1=0,"",INDEX(P: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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Sarah" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help determining a best "lineup"
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help determining a best "lineup"
Worked like a charm...Thank you so much, this is amazing!
"Max" wrote: 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help determining a best "lineup"
You're welcome, Sarah.
Glad it provided an option here .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sarah" wrote in message ... Worked like a charm...Thank you so much, this is amazing! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
"how to make data lineup from 1st to 2nd page for printing? | Excel Worksheet Functions | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |