PDA

View Full Version : Help With VBA in Excel ? Writing a function using probabilities ?



BadAtMath
03-24-2011, 11:51 AM
I need help writing a function in VB in excel that decides the winner of a game, the probabilities are input into an excel worksheet by the user. There are 2 players and 4 different probabilities for each player:

a = probability of first shot being allowed
b = probability of winning first shot
c = probability of second shot being allowed
d = probability of winning second shot

The game is played between 2 players, in each game one is chosen to be the server and the other the receiver. Service alternates game by game. A single game consists of a sequence of points played with the same player serving. A game is won when a player has (a) scored four or more points and (b) has two or more points more than his opponent

My function needs to take the server (chosen at random) and return the winner of a particular point, calculated according to the above probabilities.

I have NO idea where to start here, any help please?????

This is the code I have so far, just need to write the function :


Sub TennisSimulator()
Dim firstpointlegalA, winfirstpointA, secondpointlegalA, winsecondpointA As Integer
Dim firstpointlegalB, winfirstpointB, secondpointlegalB, winsecondpointB As Integer

'Player A's Statistics
firstpointlegalA = Cells(7, 5).Value
winfirstpointA = Cells(14, 6).Value
secondpointlegalA = Cells(19, 5).Value
winsecondpointA = Cells(26, 6).Value

'Player B's Statistics
firstpointlegalB = Cells(7, 11).Value
winfirstpointB = Cells(14, 12).Value
secondpointlegalB = Cells(19, 11).Value
winsecondpointB = Cells(26, 12).Value

'Choosing Server
x = Cells(28, 2).Value
If x <= 5 Then
MsgBox "The First Player To Serve Will Be " & Cells(4, 3).Value
Else
MsgBox "The First Player To Serve Will Be " & Cells(4, 9).Value

End If
End Sub