PDA

View Full Version : Simple simulation help



yegg
02-03-2010, 03:21 PM
So I have an assignment due tomorrow evening and I though I could teach myself enough VBA in time, but I was completely wrong. I have no idea what I'm doing.

So the problem is;
Joe and 200 other job applicants are taking a 10 question true-false test to determine who gets the job. Joe has a studied and has a 90% chance of getting each question correct, whereas the other 200 applicants are just guessing and so have only 50% chance of answering each question correctly. If more than one person ties for the highest score, the winner is chosen at random. Develop a stochastic simulation to estimate Joe's chance of getting the job. What is your estimate?

Can someone please help me?

Aussiebear
02-03-2010, 03:50 PM
Sorry but forum policy states that we do not do homework for you. However if you can attach a workbook with your data, we may be able to assist you by pointing you in the right direction. To attach a workbook, click on Go Advanced, scroll down to manage attachments and upload your file from there.

yegg
02-09-2010, 01:33 PM
Okay, so I was given an extension.

Though I have a clearer idea of how VBA works, I'm still having trouble getting this macro running smoothly. I uploaded what I have so far and any help would be much appreciated.

So thought first I would run 1000 trials which start with joe's tests followed by the tests of his rivals. If at any point his rivals gets a higher score than joe, then I would like the program to immediately move on to the next trial. I'm getting these "Next without For" errors, I'm not really sure why. Also, does anyone know how I could tabulate the rival's scores from 1 to 10 and then later compare them to joe's scores?

Thanks in advance!

Aussiebear
02-09-2010, 06:47 PM
Here's a couple of initial pointers.

Get into the habit of using Option Explicit at the start of your code. This forces you to declare your variables.

There are a number of variables in your code that you haven't declared, namely; Joechance, Otherchance, numothers, numrivals,Trials, Jtest, Rivalruns, Rtest, and Rivaltie.

The For Next errors indicate that you are using in this case Next without an accompaning For. They are required as a pairing in VBA.

In your workbook there doesn't appear to be any data. Please attach a new workbook with some data so that we can see what it is that you are working with.

Paul_Hossler
02-09-2010, 07:33 PM
As Mr. Aussiebear says, "We don't do homework." I make my own kids to theirs, and only provide a nudge in the right direction when necessary.

I assume that this is NOT for a programming class, so below are only some suggestions / comments about VBA, including one way to struture the code (I like indents). It compiles without error, BUT the logic errors are left as an excercise to the reader.:devil2:


Option Explicit
'1. This belongs (probably) in a Standard module, and
' not on the Worksheet code sheet
'2. No idea if this logic is correct (I don't think it is)
' but using For/Next loops are an example of the pairing
'3. some statements just seem to be hung on. Marked with ?????????????
' You'll have to figure them out
'4. don't need to .Select a cell to use it
'5. Rnd() returns a number in the range (0,1), so Int(Rnd () ) will always be .....

Sub proJoeschmo()
Dim trials As Long, jtest As Long
Dim joescore As Long, joechance As Long
Dim rivalscore As Long, otherchance As Long
Dim joewins As Long, numothers As Long
Dim rivalruns As Long, numrivals As Long
Dim rtest As Long, rivaltie As Long, othertie As Long

joechance = Range("B1").Value
otherchance = Range("B2").Value
numothers = Range("B3").Value

For trials = 1 To 1000
For jtest = 1 To 10
If Rnd() < 0.9 Then joescore = joescore + 1
Next jtest
For rivalruns = 1 To numrivals

rivalscore = 0

For rtest = 1 To 10
If Rnd() < 0.5 Then rivalscore = rivalscore + 1
' If rivalscore > joescore Then Next trials ???????????????????
If rivalscore > joescore Then Exit For
Next rtest

Next rivalruns
' End If ?????????????????????????????????????????

If rivalscore = joescore Then rivaltie = rivaltie + 1

' Next rivaltests ??????????????????????????
If Int(Rnd(othertie)) = 1 Then joewins = joewins + 1

Next trials

Range("B5").Value = joewins / 1000
joewins = 0

End Sub


Paul

yegg
02-10-2010, 05:22 PM
Thanks a lot for the pointers, guys. And yes, it's definitely not a programming class, this is for a stochastic models theory class.

So I made some major adjustments but I'm still having trouble with a few specific things. I outlined these things in my comments.

Sub proJoeschmo()

Dim joescore As Long
Dim rivalscore As Long
Dim rten As Long
Dim rnine As Long
Dim reight As Long
Dim rseven As Long
Dim rsix As Long
Dim rfive As Long
Dim rfour As Long
Dim rthree As Long
Dim rtwo As Long
Dim rone As Long


For trials = 1 To 1000
For jtest = 1 To 10
If Rnd() < 0.9 Then joescore = joescore + 1
Next jtest

For rivalruns = 1 To 200
For rtest = 1 To 10
If Rnd() < 0.5 Then rivalscore = rivalscore + 1
Next rtest
If rivalscore > joescore Then Exit For 'is this line, right? I want to move onto the next trial if joe outright loses
If rivalscore = 10 Then rten = rten + 1
If rivalscore = 9 Then rnine = rnine + 1
If rivalscore = 8 Then reight = reight + 1
If rivalscore = 7 Then rseven = rseven + 1
If rivalscore = 6 Then rsix = rsix + 1
If rivalscore = 5 Then rfive = rfive + 1
If rivalscore = 4 Then rfour = rfour + 1
If rivalscore = 3 Then rthree = rthree + 1
If rivalscore = 2 Then rtwo = rtwo + 1
If rivalscore = 1 Then rone = rone + 1
Next rivalruns


If joescore > rivalscore Then joewins = joewins + 1 'how can I make rivalscores equal the highest rivals' scores?
'I need to insert a "but if not, then" type of line here

If joescore = 10 Then joewins = joewins + 1 / rten + 1
If joescore = 9 Then joewins = joewins + 1 / rnine + 1
If joescore = 8 Then joewins = joewins + 1 / reight + 1
If joescore = 7 Then joewins = joewins + 1 / rseven + 1
If joescore = 6 Then joewins = joewins + 1 / rsix + 1
If joescore = 5 Then joewins = joewins + 1 / rfive + 1
If joescore = 4 Then joewins = joewins + 1 / rfour + 1
If joescore = 3 Then joewins = joewins + 1 / rthree + 1
If joescore = 2 Then joewins = joewins + 1 / rtwo + 1
If joescore = 1 Then joewins = joewins + 1 / rone + 1

joescore = 0 'is this how I set everything to zero?
rivalscore = 0
rten = 0
rnine = 0
reight = 0
rseven = 0
rsix = 0
rfive = 0
rfour = 0
rthree = 0
rtwo = 0
rone = 0
Next trials

Range("B5").Select
ActiveCell.Value = joewins / 1000



End Sub


If there are any logical errors that I've missed, please be so kind as to point them out. Thanks!

Aussiebear
02-10-2010, 05:49 PM
Did you use use Option Explicit at start of code?

yegg
02-16-2010, 05:10 PM
Yes I did.

Also here's the revised version, and it's still not working! =[
I keep getting Next without For error when there clearly is a For.

Option Explicit
Sub proJoeschmo()

Dim joescore As Long
Dim rivalscore As Long
Dim r(10) As Long
Dim bscore As Long
Dim trials As Long
Dim jtest As Long
Dim rivalruns As Long
Dim rtest As Long


For trials = 1 To 1000
For jtest = 1 To 10
If Rnd() < 0.9 Then joescore = joescore + 1
Next jtest

For rivalruns = 1 To 200
For rtest = 1 To 10
If Rnd() < 0.5 Then rivalscore = rivalscore + 1
Next rtest
If rivalscore > joescore Then
Exit For
If rivalscore = 10 Then
r(10) = r(10) + 1
If rivalscore = 9 Then
r(9) = r(9) + 1
If rivalscore = 8 Then
r(8) = r(8) + 1
If rivalscore = 7 Then
r(7) = r(7) + 1
If rivalscore = 6 Then
r(6) = r(6) + 1
If rivalscore = 5 Then
r(5) = r(5) + 1
If rivalscore = 4 Then
r(4) = r(4) + 1
If rivalscore = 3 Then
r(3) = r(3) + 1
If rivalscore = 2 Then
r(2) = r(2) + 1
If rivalscore = 1 Then
r(1) = r(1) + 1
If rivalscore = 1 Then
r(0) = r(0) + 1
Next rivalruns

If r(10) > 0 Then bscore = 10
ElseIf r(9) > 0 Then bscore = 9
ElseIf r(8) > 0 Then bscore = 8
ElseIf r(7) > 0 Then bscore = 7
ElseIf r(6) > 0 Then bscore = 6
ElseIf r(5) > 0 Then bscore = 5
ElseIf r(4) > 0 Then bscore = 4
ElseIf r(3) > 0 Then bscore = 3
ElseIf r(2) > 0 Then bscore = 2
ElseIf r(1) > 0 Then bscore = 1
ElseIf r(0) > 0 Then bscore = 0
End If

If joescore > bscore Then
joewins = joewins + 1


Else
If joescore = 10 Then joewins = joewins + (1 / (r(10) + 1))
If joescore = 9 Then joewins = joewins + (1 / (r(9) + 1))
If joescore = 8 Then joewins = joewins + (1 / (r(8) + 1))
If joescore = 7 Then joewins = joewins + (1 / (r(7) + 1))
If joescore = 6 Then joewins = joewins + (1 / (r(6) + 1))
If joescore = 5 Then joewins = joewins + (1 / (r(5) + 1))
If joescore = 4 Then joewins = joewins + (1 / (r(4) + 1))
If joescore = 3 Then joewins = joewins + (1 / (r(3) + 1))
If joescore = 2 Then joewins = joewins + (1 / (r(2) + 1))
If joescore = 1 Then joewins = joewins + (1 / (r(1) + 1))
If joescore = 0 Then joewins = joewins + (1 / (r(0) + 1))
End If
joescore = 0
rivalscore = 0

For i = 1 To 10
r(i) = 0
Next i

Next trials

Range("B5").Select
ActiveCell.Value = joewins / 1000



End Sub

yegg
02-16-2010, 05:10 PM
What the heck? I didn't indent that If statements like that.

Paul_Hossler
02-16-2010, 06:06 PM
I'm still not sure about your logic, etc. but re-formatting your code into more bite size pieces each performing a more focused task, adding comments, removing long complicated If/Then/Elseif/ElseIf ...... structures, but most importantly (IMHO) is to use the Array in a very different manner.

With some work, this could still be greatly simplified since I know that there's a lot of unneeded code being executed, but Ijust wanted to offer alternatives about the use of VBA and structuring.

Code is for the programmer, not the computer, so it's worth it IMHO again to have more maintainable and debuggable code even if a few cycles are wasted



Option Explicit
Sub proJoeschmo()

Dim Answers() As Long
'row = 0 for Joe, 1 to 200 for others
Dim iAnswer As Long, iPerson As Long, iTrial As Long
Dim iJoeWins As Long

For iTrial = 1 To 1000

'clear array for next trial
ReDim Answers(0 To 200)

'Joe First
iPerson = 0
For iAnswer = 1 To 10
If Rnd() < 0.9 Then
Answers(iPerson) = Answers(iPerson) + 1
End If
Next iAnswer

'next other people
For iPerson = 1 To 200
For iAnswer = 1 To 10
If Rnd() < 0.5 Then
Answers(iPerson) = Answers(iPerson) + 1
End If
Next iAnswer
Next iPerson


'count Joe wins
iJoeWins = 0
For iPerson = 1 To 200
If Answers(iPerson) < Answers(0) Then
iJoeWins = iJoeWins + 1
End If
Next iPerson

'write number of Joe wins, one Trail per row in worksheet
ActiveSheet.Cells(iTrial, 1).Value = iJoeWins

Next iTrial

End Sub


Paul

SamT
02-16-2010, 07:56 PM
'Run 1000 trials
For trials = 1 To 1000

'Give Joe a 90% chance at a score of 9
For jtest = 1 To 10
If Rnd() < 0.9 Then joescore = joescore + 1
Next jtest

For rivalruns = 1 To 200

'Give a good probability that rivalscore is 5
For rtest = 1 To 10
If Rnd() < 0.5 Then rivalscore = rivalscore + 1
Next rtest

'The odds are slim and none that this will happen
If rivalscore > joescore Then
Exit For

'Fill an Array with the frequency of rivalscore
'
'Way more than seven nested If statements and no End Ifs in sight.
'This is a good place for a Case Statement
If rivalscore = 10 Then
r(10) = r(10) + 1
If rivalscore = 9 Then
r(9) = r(9) + 1
If rivalscore = 8 Then
r(8) = r(8) + 1
If rivalscore = 7 Then
r(7) = r(7) + 1
If rivalscore = 6 Then
r(6) = r(6) + 1
If rivalscore = 5 Then
r(5) = r(5) + 1
If rivalscore = 4 Then
r(4) = r(4) + 1
If rivalscore = 3 Then
r(3) = r(3) + 1
If rivalscore = 2 Then
r(2) = r(2) + 1
If rivalscore = 1 Then
r(1) = r(1) + 1
If rivalscore = 1 Then
r(0) = r(0) + 1

'Repeat the above 200 times for rivalscore
Next rivalruns

'Assign bscore the value of the lowest index slot in r(10) that contains a value > 0.
'
If r(10) > 0 Then bscore = 10
ElseIf r(9) > 0 Then bscore = 9
ElseIf r(8) > 0 Then bscore = 8
ElseIf r(7) > 0 Then bscore = 7
ElseIf r(6) > 0 Then bscore = 6
ElseIf r(5) > 0 Then bscore = 5
ElseIf r(4) > 0 Then bscore = 4
ElseIf r(3) > 0 Then bscore = 3
ElseIf r(2) > 0 Then bscore = 2
ElseIf r(1) > 0 Then bscore = 1
ElseIf r(0) > 0 Then bscore = 0
End If

If joescore > bscore Then
joewins = joewins + 1

'The odds of Joe winning are the odds that Rnd() will be less than .9 plus the odds that all 200 runs of Rnd() will be higher than .9


'The next badly nested If statement tries to add a Single to a Long, IOW, does nothing.
Else
If joescore = 10 Then joewins = joewins + (1 / (r(10) + 1))
If joescore = 9 Then joewins = joewins + (1 / (r(9) + 1))
If joescore = 8 Then joewins = joewins + (1 / (r(8) + 1))
If joescore = 7 Then joewins = joewins + (1 / (r(7) + 1))
If joescore = 6 Then joewins = joewins + (1 / (r(6) + 1))
If joescore = 5 Then joewins = joewins + (1 / (r(5) + 1))
If joescore = 4 Then joewins = joewins + (1 / (r(4) + 1))
If joescore = 3 Then joewins = joewins + (1 / (r(3) + 1))
If joescore = 2 Then joewins = joewins + (1 / (r(2) + 1))
If joescore = 1 Then joewins = joewins + (1 / (r(1) + 1))
If joescore = 0 Then joewins = joewins + (1 / (r(0) + 1))
End If
joescore = 0
rivalscore = 0

For i = 1 To 10
r(i) = 0
Next i

Next trials

'I'll give ten to one odds that the value in B5 would be >.999 if this sub worked.

Range("B5").Select
ActiveCell.Value = joewins / 1000

Aussiebear
02-16-2010, 11:16 PM
Wrapping your code in the VBA tags, as we would like you to do when posting code to these forums, invokes a routine which indents lines as code as per the rules. It makes the code so much easier to read.