HELP with the RAND() Function......AGAIN!!!!!

[denise1082 via OfficeKB.com]

Hello All,
Thank you guys for the input on my last question. I got that answer correct.
I have two more RAND functions 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...here is question #2

#2)Jack is taking a vacation at a tropical resort. After having too much to
drink at a bar he attemps to make it back to the hotel by crossing the pier
which is 60 long but only 8 steps wide. jack is headed towards the hotel.
Each time he takes a step, there is a 70%chance he steps forward, toward the
hotel, a 10% chance he stumbles a step backwards, a 10% chance he takes a
step to his left, towards the ocean and a 10% chance he takes a step right,
towards the ocean.

If jack ever makes it back to the hotel he will be fine. If he ever ends up
5 steps to the left or 5 steps to the right, he will fall into the ocean.
Once he is in the ocean this voyage is over. After 100 steps if jack is
still on the pier he will collapse.

Create an excel worksheet to simulate jack's random walk back to the hotel.
There should be 100 rows in this worksheet, one for each potential step. At
the opt of the worksheet there should be a cell that gives Jack's final
result: either "hotel" or "ocean" or "collapse of pier".

In this question, I can't figure out how to keep count of the steps.


I know these questions seem lengthy but any resonse will be greatly
appreciated!!!! Thank you so much for your help!!!!

 

[Dana DeLouis]

Here's a quick-n-dirty vba solution to #2. Run "Walk_Hotel" with a blank
worksheet.
The sub "Odds" ran 100 times, and shows success only about 25%-30% of the
time.
Again, this is not fully tested. Just quick n dirty.
vba library set to use atpvbaen.xls for RandBetween

Sub Walk_Hotel()
Dim Step As Long
Dim R As Long
Dim C As Long

R = 1
C = 7
'valid width 3-11
[A:L].Clear
With [C1:K60]
.Interior.ColorIndex = 40
.HorizontalAlignment = xlCenter
End With
[B1:B60].Interior.ColorIndex = 8
[L1:L60].Interior.ColorIndex = 8
[B61:L61].Interior.ColorIndex = 8

Cells(R, C) = 0
For Step = 1 To 100
Select Case RandBetween(1, 100)
Case 1 To 10 'Backwards
R = R - 1
Case 11 To 20 'Left
C = C - 1
Case 21 To 30 'Right
C = C + 1
Case 31 To 100 'Forward
R = R + 1
End Select

If R = 0 Then
[A1] = "Ocean"
Exit Sub
End If

Cells(R, C) = Cells(R, C) & " " & Step
If C = 2 Or C = 12 Then
[A1] = "Ocean"
Exit Sub
ElseIf R = 60 Then
[A1] = "Hotel"
Exit Sub
End If
Next Step
End Sub


Sub Odds()
Dim j
Dim Count As Long
For j = 1 To 100
Walk_Hotel
If [A1] = "Hotel" Then Count = Count + 1
Next j
MsgBox Count / 100
End Sub

 

[Max]

Here's a crack at your 2nd question ..
(your subject line was not apt, it isn't just about RAND(), it's a whole lot
more, and probably closer to: Simulation in operations research using Excel
or something like that <g>)

A sample construct of the simulation set-up is available at:
http://www.savefile.com/files/9455904
Simulation of 100 random steps (Drunken Walk).xls

Set-up a 2 col vlookup table in say, I3:J6

0% B
10% L
20% R
30% F

In J3:J6 are the letters: B, L, R, F,
denoting the 4 possible outcomes from each step:

B = Backwards
L = Left
R = Right
F = Forwards

while I3:I6 houses the known probabilities for the 4 possible outcomes,
"stacked" up in ascending order (just type in the figs with the percent sign)
Note that I3:I6 has to be sorted in ascending order

Then put in B3:
=VLOOKUP(RAND(),$I$3:$J$6,2,TRUE)

Place the 4 outcome labels in C2:F2 : B, L, R, F

Put in C3:
=IF($B3=C$2,1,"")
Copy C3 across to F3

Put in G3:
=IF(ABS(SUM(D$33)-SUM(E$3:E3))>=5,"Ocean",IF(SUM(F$3:F3)-SUM(C$3:C3)>=60,"Hotel",""))

Select B3:G3, copy down to G102

B3:B102 will return the simulated random results for each of the 100 steps

Cols C to F will convert the "letter" results of each randomized step within
B3:B102 into a simple number "1" under the correct col header

Col G will then monitor the progressive results of Jack's possible 100
step-wise meanderings, ie either: B, L, R, F, and return accordingly

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,$G$3:$G$102<>"",0)),"Collapse on
pier",INDEX($G$3:$G$102,MATCH(TRUE,$G$3:$G$102<>"",0)))

B2 will return the required end result of Jack's 100 simulated random steps
by reading col G, viz. either: "Hotel" or "Ocean", or "Collapse on pier" if
neither of the earlier outcomes transpire.

Pressing F9 key will recalc afresh the simulation

denise1082 via OfficeKB.com" wrote: ---
#2)Jack is taking a vacation at a tropical resort. After having too much to
drink at a bar he attemps to make it back to the hotel by crossing the pier
which is 60 [steps] long but only 8 steps wide. jack is headed towards the hotel.
Each time he takes a step, there is a 70% chance he steps forward, toward the
hotel, a 10% chance he stumbles a step backwards, a 10% chance he takes a
step to his left, towards the ocean and a 10% chance he takes a step right,
towards the ocean.

If jack ever makes it back to the hotel he will be fine. If he ever ends up
5 steps to the left or 5 steps to the right, he will fall into the ocean.
Once he is in the ocean this voyage is over. After 100 steps if jack is
still on the pier he will collapse.

Create an excel worksheet to simulate jack's random walk back to the hotel.
There should be 100 rows in this worksheet, one for each potential step. At
the top of the worksheet there should be a cell that gives Jack's final
result: either "hotel" or "ocean" or "collapse of pier"

 

[denise1082 via OfficeKB.com]

Max,
Thank you so much!!!! Your help was greatly needed. How did you come up
with all of those functions so quickly? I have been working on these
problems for a week now. Thanks again!!!

Here's a crack at your 2nd question ..
(your subject line was not apt, it isn't just about RAND(), it's a whole lot
more, and probably closer to: Simulation in operations research using Excel
or something like that <g>)

A sample construct of the simulation set-up is available at:
http://www.savefile.com/files/9455904
Simulation of 100 random steps (Drunken Walk).xls

Set-up a 2 col vlookup table in say, I3:J6

0% B
10% L
20% R
30% F

In J3:J6 are the letters: B, L, R, F,
denoting the 4 possible outcomes from each step:

B = Backwards
L = Left
R = Right
F = Forwards

while I3:I6 houses the known probabilities for the 4 possible outcomes,
"stacked" up in ascending order (just type in the figs with the percent sign)
Note that I3:I6 has to be sorted in ascending order

Then put in B3:
=VLOOKUP(RAND(),$I$3:$J$6,2,TRUE)

Place the 4 outcome labels in C2:F2 : B, L, R, F

Put in C3:
=IF($B3=C$2,1,"")
Copy C3 across to F3

Put in G3:
=IF(ABS(SUM(D$33)-SUM(E$3:E3))>=5,"Ocean",IF(SUM(F$3:F3)-SUM(C$3:C3)>=60,"Hotel",""))

Select B3:G3, copy down to G102

B3:B102 will return the simulated random results for each of the 100 steps

Cols C to F will convert the "letter" results of each randomized step within
B3:B102 into a simple number "1" under the correct col header

Col G will then monitor the progressive results of Jack's possible 100
step-wise meanderings, ie either: B, L, R, F, and return accordingly

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,$G$3:$G$102<>"",0)),"Collapse on
pier",INDEX($G$3:$G$102,MATCH(TRUE,$G$3:$G$102<>"",0)))

B2 will return the required end result of Jack's 100 simulated random steps
by reading col G, viz. either: "Hotel" or "Ocean", or "Collapse on pier" if
neither of the earlier outcomes transpire.

Pressing F9 key will recalc afresh the simulation

denise1082 via OfficeKB.com" wrote: ---
#2)Jack is taking a vacation at a tropical resort. After having too much to
drink at a bar he attemps to make it back to the hotel by crossing the pier

[quoted text clipped - 13 lines]

the top of the worksheet there should be a cell that gives Jack's final
result: either "hotel" or "ocean" or "collapse of pier"

 

[Max]

You're welcome !
Thanks for callback ..

 

[Bob Phillips]

And homework to boot!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Here's a crack at your 2nd question ..
(your subject line was not apt, it isn't just about RAND(), it's a whole lot
more, and probably closer to: Simulation in operations research using Excel
or something like that <g>)

A sample construct of the simulation set-up is available at:
http://www.savefile.com/files/9455904
Simulation of 100 random steps (Drunken Walk).xls

Set-up a 2 col vlookup table in say, I3:J6

0% B
10% L
20% R
30% F

In J3:J6 are the letters: B, L, R, F,
denoting the 4 possible outcomes from each step:

B = Backwards
L = Left
R = Right
F = Forwards

while I3:I6 houses the known probabilities for the 4 possible outcomes,
"stacked" up in ascending order (just type in the figs with the percent sign)
Note that I3:I6 has to be sorted in ascending order

Then put in B3:
=VLOOKUP(RAND(),$I$3:$J$6,2,TRUE)

Place the 4 outcome labels in C2:F2 : B, L, R, F

Put in C3:
=IF($B3=C$2,1,"")
Copy C3 across to F3

Put in G3:
=IF(ABS(SUM(D$33)-SUM(E$3:E3))>=5,"Ocean",IF(SUM(F$3:F3)-SUM(C$3:C3)>=60,"
Hotel",""))

Select B3:G3, copy down to G102

B3:B102 will return the simulated random results for each of the 100 steps

Cols C to F will convert the "letter" results of each randomized step within
B3:B102 into a simple number "1" under the correct col header

Col G will then monitor the progressive results of Jack's possible 100
step-wise meanderings, ie either: B, L, R, F, and return accordingly

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,$G$3:$G$102<>"",0)),"Collapse on
pier",INDEX($G$3:$G$102,MATCH(TRUE,$G$3:$G$102<>"",0)))

B2 will return the required end result of Jack's 100 simulated random steps
by reading col G, viz. either: "Hotel" or "Ocean", or "Collapse on pier" if
neither of the earlier outcomes transpire.

Pressing F9 key will recalc afresh the simulation

denise1082 via OfficeKB.com" wrote: ---
#2)Jack is taking a vacation at a tropical resort. After having too much to
drink at a bar he attemps to make it back to the hotel by crossing the pier
which is 60 [steps] long but only 8 steps wide. jack is headed towards the hotel.
Each time he takes a step, there is a 70% chance he steps forward, toward the
hotel, a 10% chance he stumbles a step backwards, a 10% chance he takes a
step to his left, towards the ocean and a 10% chance he takes a step right,
towards the ocean.

If jack ever makes it back to the hotel he will be fine. If he ever ends up
5 steps to the left or 5 steps to the right, he will fall into the ocean.
Once he is in the ocean this voyage is over. After 100 steps if jack is
still on the pier he will collapse.

Create an excel worksheet to simulate jack's random walk back to the hotel.
There should be 100 rows in this worksheet, one for each potential step. At
the top of the worksheet there should be a cell that gives Jack's final
result: either "hotel" or "ocean" or "collapse of pier"

 

[Max]

And homework to boot!

Ah, but with "home" disguised, it's still all about work <g>.
Albeit in this post, guess it wasn't even disguised.

 

[joeu2004]

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 D19 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.

 

[Max]

Just a little OT, Dana, since you were in town <g>
There's an outstanding request, re: http://tinyurl.com/lgonp
for a glimpse of your Sub "UnrankKSubset"
Is it still on, or shall I regard it as closed?
Either way, pl drop me a line in closure. Thanks.

 

[Bob Phillips]

I've often been accused myself of answering homework questions Max, but here
she even quoted the question from the paper . Don't tell Harlan<g>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Max]

I've often been accused myself of answering homework questions Max, but here
she even quoted the question from the paper . Don't tell Harlan<g>

Ay, even working adults bring work home to complete (at times much more or
more often than they really should). These people should not be "denied"
excel help sought in the newsgroups simply because they're doing "homework"
<g>.