Simulation in operations research using Excel

[denise1082 via OfficeKB.com]

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.

 

[Max]

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

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",INDEX($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
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

....

 

[denise1082 via OfficeKB.com]

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

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

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",INDEX($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

...

 

[Max]

You are welcome !
Thanks for posting back ..