Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simulation in operations research using Excel
Hello All,
Thank you guys for the input on my last question. I got that answer correct. I have another RAND function that I cannot figure out...this RAND function is just not my thing. I'll just list the quesetions....it's easier to understand that way. #1)There is 1 minute 50 seconds left to go in the championship football game. Our team is down by 5 points. We recover the ball on the opponent's 45 yard line. We have no time-outs left and there is enough time on the clock for exactly 8 plays. Coach figures that on each play these are the probabilities: 20%-gain 7 yards 10%-gain 15 yards 10%-gain 25 yards 10%gain 3 yards 10%-sack, lose 10 yards 10%-interception 28%incomplete. no gain or loss 2%-gain 60 yards a) simulate the rest of the game. If you cross the goal line you win. If you don't make it to the goal lne or there is an interception, you lose. Have a singe cell at the top that givese the result of the game, either win or lose. Dont worry about the 1st downs B)Do worry about the 1st downs. If you do four down without a net gain of 10 yards after any down you lose. I can't figure out the formula to add or subtract the yards successfully while getting a "win" or "lose" result at the same time. Thanks for your help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simulation in operations research using Excel
Along similar construct lines as that done for the earlier 100 random steps
drunken walk sample, here's a possible set-up for this part of the question (I don't understand American Football, the rules are so complicated <g) a) simulate the rest of the game. .. Sample construct available at: http://www.savefile.com/files/9498068 Simulation of 8 consecutive end-game plays (American Football).xls Set-up a 2 col vlookup table in say, N3:O10 0% G60 2% I 12% G3 22% G15 32% G25 42% G7 62% G-10 72% G0 In O3:O10 are the 8 possible outcomes for each play, ie either: G60, I, G3, G15, G25, G7, G-10, or G0 where G60 = Gain 60 yds I = Intercept G3 = Gain 3 yds G15 = Gain 15 yds G25 = Gain 25 yds G7 = Gain 7 yds G-10 = Sack (ie Gain -10 yds) G0 = Incomplete (ie Gain 0 yds) while N3:N10 houses the corresponding known probabilities for the 8 possible outcomes, "stacked" up in ascending order (just type in the figs with the percent sign). Note that N3:N10 has to be set-up in sorted ascending order. Place the 8 possible outcome labels into C2:J2 : G60, I, G3, G15, G25, G7, G-10, or G0 Label K2 as: Play-by-play result Label L2 as: Cumulative result Then put In B3: =VLOOKUP(RAND(),$N$3:$O$10,2,TRUE) In C3: =IF($B3=C$2,IF(LEFT($B3,1)="G",SUBSTITUTE(C$2,"G", "")+0,C$2),"") Copy C3 across to J3 In K3: =IF(ISNUMBER(MATCH("I",C3:J3,0)),"Lose",SUM(C3:J3) ) In L3: =IF(K3="Lose","Lose",IF(SUM($K$3:K3)=55,"Win","") ) Select B3:L3, copy down to L8 B3:B8 will return the simulated outcomes for each of the 8 plays Cols C to J will translate the outcome results of each randomized play within B3:B10 into corresponding numbers under the col header for "G" prefix outcomes (eg: G60 becomes 60, G7 becomes 7, etc) or return "I" for intercept outcomes. These play-by-play results are then consolidated into a single col K. Col L will then monitor the progressive results of the max 8 consecutive plays in col K, cumulating numeric yard gains/losses to see whether there's a net gain of 55 yards (as 100 yds - 45 yds = 55 yds net gain to the Goal line is required for a "Win"), or, returning "Lose" where interception occurs. Then to produce the final end result, put in say, B2's formula bar, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(ISNA(MATCH(TRUE,$L$3:$L$10<"",0)),"Lose",INDE X($L$3:$L$10,MATCH(TRUE,$L$3:$L$10<"",0))) B2 will return the required end result, either "Win" or "Lose" Pressing F9 key will recalc afresh the simulation -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "denise1082 via OfficeKB.com" wrote: .... #1)There is 1 minute 50 seconds left to go in the championship football game. Our team is down by 5 points. We recover the ball on the opponent's 45 yard line. We have no time-outs left and there is enough time on the clock for exactly 8 plays. Coach figures that on each play these are the probabilities: 20%-gain 7 yards 10%-gain 15 yards 10%-gain 25 yards 10%gain 3 yards 10%-sack, lose 10 yards 10%-interception 28%incomplete. no gain or loss 2%-gain 60 yards a) simulate the rest of the game. If you cross the goal line you win. If you don't make it to the goal lne or there is an interception, you lose. Have a single cell at the top that gives the result of the game, either win or lose. Don't worry about the 1st downs .... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simulation in operations research using Excel
Max,
You are a blessing. Thank you so much for all of you help tonight!!! You really don't know how much you have helped me. Thanks again!!!!!!!!!!!!!! Max wrote: Along similar construct lines as that done for the earlier 100 random steps drunken walk sample, here's a possible set-up for this part of the question (I don't understand American Football, the rules are so complicated <g) a) simulate the rest of the game. .. Sample construct available at: http://www.savefile.com/files/9498068 Simulation of 8 consecutive end-game plays (American Football).xls Set-up a 2 col vlookup table in say, N3:O10 0% G60 2% I 12% G3 22% G15 32% G25 42% G7 62% G-10 72% G0 In O3:O10 are the 8 possible outcomes for each play, ie either: G60, I, G3, G15, G25, G7, G-10, or G0 where G60 = Gain 60 yds I = Intercept G3 = Gain 3 yds G15 = Gain 15 yds G25 = Gain 25 yds G7 = Gain 7 yds G-10 = Sack (ie Gain -10 yds) G0 = Incomplete (ie Gain 0 yds) while N3:N10 houses the corresponding known probabilities for the 8 possible outcomes, "stacked" up in ascending order (just type in the figs with the percent sign). Note that N3:N10 has to be set-up in sorted ascending order. Place the 8 possible outcome labels into C2:J2 : G60, I, G3, G15, G25, G7, G-10, or G0 Label K2 as: Play-by-play result Label L2 as: Cumulative result Then put In B3: =VLOOKUP(RAND(),$N$3:$O$10,2,TRUE) In C3: =IF($B3=C$2,IF(LEFT($B3,1)="G",SUBSTITUTE(C$2,"G" ,"")+0,C$2),"") Copy C3 across to J3 In K3: =IF(ISNUMBER(MATCH("I",C3:J3,0)),"Lose",SUM(C3:J3 )) In L3: =IF(K3="Lose","Lose",IF(SUM($K$3:K3)=55,"Win","" )) Select B3:L3, copy down to L8 B3:B8 will return the simulated outcomes for each of the 8 plays Cols C to J will translate the outcome results of each randomized play within B3:B10 into corresponding numbers under the col header for "G" prefix outcomes (eg: G60 becomes 60, G7 becomes 7, etc) or return "I" for intercept outcomes. These play-by-play results are then consolidated into a single col K. Col L will then monitor the progressive results of the max 8 consecutive plays in col K, cumulating numeric yard gains/losses to see whether there's a net gain of 55 yards (as 100 yds - 45 yds = 55 yds net gain to the Goal line is required for a "Win"), or, returning "Lose" where interception occurs. Then to produce the final end result, put in say, B2's formula bar, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(ISNA(MATCH(TRUE,$L$3:$L$10<"",0)),"Lose",IND EX($L$3:$L$10,MATCH(TRUE,$L$3:$L$10<"",0))) B2 will return the required end result, either "Win" or "Lose" Pressing F9 key will recalc afresh the simulation ... #1)There is 1 minute 50 seconds left to go in the championship football game. Our team is down by 5 points. We recover the ball on the opponent's 45 yard [quoted text clipped - 9 lines] 28%incomplete. no gain or loss 2%-gain 60 yards a) simulate the rest of the game. If you cross the goal line you win. If you don't make it to the goal lne or there is an interception, you lose. Have a single cell at the top that gives the result of the game, either win or lose. Don't worry about the 1st downs ... -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simulation in operations research using Excel
You are welcome !
Thanks for posting back .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "denise1082 via OfficeKB.com" wrote: Max, You are a blessing. Thank you so much for all of you help tonight!!! You really don't know how much you have helped me. Thanks again!!!!!!!!!!!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 5 and Excel 2000 question. | Excel Discussion (Misc queries) | |||
Value Errors with EXCEL XP not showing up in EXCEL 2000 | Links and Linking in Excel | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
How can i made a monte Carlo simulation with Excel ? | Excel Discussion (Misc queries) | |||
Cancelling shortcut ctrl-click which opens "Research" in Excel 200 | Excel Discussion (Misc queries) |