denise1082 via OfficeKB.com wrote:
I have two more RAND functions that I cannot figure out...this RAND function
is just not my thing.

It is not always an easy thing to use. It might help to realize that
RAND() returns a fraction less than one and greater than or equal to
zero. Thus, you can use the RAND() result directly as the probability
-- although it will never return 100%. For example, if RAND() returns
0.1234, you can use that as the probability 12.34%.

#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

The following solution might not be the most elegant, but I hope it is
clear.

Set up 8 cells, say B2:B9, each with the following formula [1]:

=lookup(rand(), {0,0.2,0.3,0.4,0.5,0.6,0.7,0.98},
{7,15,25,3,-10,-100,0,60})

B2:B9 represents the outcomes of 8 random events; each cell contains
the yardage gained or loss. If RAND() returns a value of zero or more
less than 0.2 (i.e. 20% of the time), LOOKUP() returns 7; if RAND()
returns a value of 0.2 or more and less than 0.3 (i.e. 10% of the
time), LOOKUP() returns 15; etc. Note that an interception (loss) is
represented by a yardage loss of -100. You will see how that fits in
below.

Use C1:C9 to keep track of the forward advance; that is, the yardline
number. C1 is the initial position (45). Put the following formula
into C2 and copy it down through C9:

=if(or(C1=100,C1<=0), C1, if(B2=100, B2, C1-B2))

Thus, C9 will represent the most forward advance at the end of the
game.

Finally, put the following formula in A1:

=if(C9<=0, "win", "lose")

I am numbering the yardlines as follows: 0 is the goalline that we are
aiming for, and 100 is the opposite goalline behind us. If we reach
yardline 0 (perhaps before the 8th event), the game is over and we win;
if we reach yardline 100 (perhaps before the 8th event), the game is
over and we lose. That is why an interception (loss) is represented by
-100: it is ensures that we are pushed back to yardline 100 or beyond.

Caveat: To be honest, I never can remember what is meant by "the
opponent's 45 yardline". As you may know, yardlines are numbered from
0 to 50 from both ends of the field. So if we are at the 45 yardline
before the 50 yardline in the direction that we are going, is that "our
45 yardline" or "the opponent's 45 yardline"? I believe it is "our 45
yardline". If it is "the opponent's 45 yardline", then set C1 to 55,
not 45. Hopefully, you know, or an American football enthusiast can
clarify this for you. If not, I would ask the teacher or at least
state your assumption in your solution.

B)Do worry about the 1st downs. If you do four down without a net gain of 10
yards after any down you lose.

Again, not an elegant solution, but hopefully a straight-forward one.

Use D1:D9 to keep track of the "down". D1 is the initial "down" (1).
Put the following formula into D2 and copy it down through D9:

=if(E1-C2=10, 1, D1+1)

Note: This permits the "down" counter to exceed 4. That is works
because I write "D1=4" instead of "D1=4" in C2 below. You might
prefer to write max(4,D1+1), if only for aesthetic purposes.

Use E1:E9 to keep track of the 1st-down yardline. E1 is the initial
position (=C1). Put the following formula into E2 and copy it down
through E9:

=if(D2=1, C2, E1)

Change the formula in C2 as follows and copy it down through C9:

=if(or(C1=100,C1<=0), C1, if(B2=100, B2, if(and(D1=4,E1-C1+B2<10),
100, C1-B2)))

Caveat emptor: I made some effort to test these formulas to a degree.
But I cannot guarantee their correctness.


-----
[1] You can also write:

=lookup(rand(), {0,7; 0.2,15; 0.3,25; 0.4,3; 0.5,-10; 0.6,-100; 0.7,0;
0.98,60})