Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Probabilities, random numbers and dice throws
Greetings, I have been trying to make a worksheet in excel 2003 to calculate probabilities associated with dice throws... I am setting it up so that i can define the number of dice i want to roll and also the number of sides they have... I found that rand between is a good way to simulate it... My problem is with probabilities. Is there a function or a way to automatically calculate the probability of having a result of x as the sum of y dice with z sides each? The prob function requires you to have an array with results and probabilities... It is too cumbersome to make by hand for... say... 40 eight sided dice! Thanks in advance :) -- Galamdring ------------------------------------------------------------------------ Galamdring's Profile: http://www.excelforum.com/member.php...o&userid=25459 View this thread: http://www.excelforum.com/showthread...hreadid=389015 |
#2
|
|||
|
|||
I don't know why you would have problems with doing it by hand. there are
only 10 to the 36 cominations for 40 eight sided dies. You can do combination probabilities, but hey get pretty complex, to do similar things, I have calculated the loest probablity numbers and done monte carlos to get the higher probability combos. The lowest probability ones can be calculated fairly easily. probability of 8 or 320 is 1/(8 raised to the (40)) probability of 9 or 319 is 40/(8 raised to the (40)) probability of 10 or 318 is (40+(40*39)/2)/(8 raised to the (40)) etc. a monte carlo which would do what you want would be sub montedie() dim output(320) as interger dim die as integer dim tot as integer dim nm as integer for nm = 1 to 1000000 tot = 0 for die = 1 to 40 tot=tot+randbetween(1,8) next die output(tot)=output(tot)+1 next nm for r = 1 to 320 cells(r,1)=r cells(r,2)=output(r) next nm end sub There are of course many ways to do this. "Galamdring" wrote: Greetings, I have been trying to make a worksheet in excel 2003 to calculate probabilities associated with dice throws... I am setting it up so that i can define the number of dice i want to roll and also the number of sides they have... I found that rand between is a good way to simulate it... My problem is with probabilities. Is there a function or a way to automatically calculate the probability of having a result of x as the sum of y dice with z sides each? The prob function requires you to have an array with results and probabilities... It is too cumbersome to make by hand for... say... 40 eight sided dice! Thanks in advance :) -- Galamdring ------------------------------------------------------------------------ Galamdring's Profile: http://www.excelforum.com/member.php...o&userid=25459 View this thread: http://www.excelforum.com/showthread...hreadid=389015 |
#3
|
|||
|
|||
A particular ordered roll of dice occurs with probability 1/z^y,
multiply that by the number of ordered rolls, c, that can give a particular sum. Assuming that the faces are numbered 1,2,...,z sum c <y 0 y 1 y+1 y=COMBIN(y,1) y+2 y(y+1)/2=COMBIN(y,1)+COMBIN(y,2) y+3 y(y+1)(y+2)/6 =COMBIN(y,1)+MULTINOMIAL(1,1,y-2)+MULTINOMIAL(1,1,1,y-3)/3! ... The logic for y+3 is that - one die could have 4, with the rest all 1's - one die could have 3, another could have 2, with the rest all 1's - three dice could have 2, with the rest all 1's In the last case, MULTINOMIAL(1,1,1,y-3) is the number of ways to choose locations that do not contain 1, but since all of those locations contain the same value, MULTINOMIAL will overcount by a factor of 3! You have to be careful to not overcount cases where multiple dice contain the same value. You also have to be careful for y+k where k=z, since it is no longer possible to have y-1 dice all with 1's and the rest of the sum on a single die. For 1<k<z, sum(c[i],i=1..y+k) = Product(y+i,i=1..k)/i! which gives individual c's by subtraction, but this will overcount for k=z. You can take advantage of symmetry, since Pr(sum=y*z-k) = Pr(sum=y+k) for k=0 Jerry Galamdring wrote: Greetings, I have been trying to make a worksheet in excel 2003 to calculate probabilities associated with dice throws... I am setting it up so that i can define the number of dice i want to roll and also the number of sides they have... I found that rand between is a good way to simulate it... My problem is with probabilities. Is there a function or a way to automatically calculate the probability of having a result of x as the sum of y dice with z sides each? The prob function requires you to have an array with results and probabilities... It is too cumbersome to make by hand for... say... 40 eight sided dice! Thanks in advance :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|