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