Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate probability
Column a is a list of possible results that can be generated by the use of 4
six sided dice together ranging from 4-24. What I want to calculate in column B is the number of combinations that the dice can produce that would total to any given result. How do I do this? Can I do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate probability
Assuming fair dice
4 can occur only if each die shows a 1: prob=1/6^4 5 can occur if 1 die shows 2 and the rest show 1: prob=COMBIN(4,1)/6^4 6 can occur if 1 die shows 3 and the rest show 1: prob=COMBIN(4,1)/6^4 or if 2 dice show 2 and the rest show 1: prob=COMBIN(4,2)/6^4 (add the two probabilities) 7 can occur if 1 die shows 4 and the rest show 1: prob=COMBIN(4,1)/6^4 or if 1 die shows 3, 1 die shows 2 and the rest show 1: prob=MULTINOMIAL(1,1,2)/6^4 or if 3 dice show 2 and 1 die shows 1: prob=COMBIN(4,1)/6^4 .... Jerry Al wrote: Column a is a list of possible results that can be generated by the use of 4 six sided dice together ranging from 4-24. What I want to calculate in column B is the number of combinations that the dice can produce that would total to any given result. How do I do this? Can I do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate probability
So how do I turn all of that into something reasonably neat use in my
caculations for column B? "Jerry W. Lewis" wrote: Assuming fair dice 4 can occur only if each die shows a 1: prob=1/6^4 5 can occur if 1 die shows 2 and the rest show 1: prob=COMBIN(4,1)/6^4 6 can occur if 1 die shows 3 and the rest show 1: prob=COMBIN(4,1)/6^4 or if 2 dice show 2 and the rest show 1: prob=COMBIN(4,2)/6^4 (add the two probabilities) 7 can occur if 1 die shows 4 and the rest show 1: prob=COMBIN(4,1)/6^4 or if 1 die shows 3, 1 die shows 2 and the rest show 1: prob=MULTINOMIAL(1,1,2)/6^4 or if 3 dice show 2 and 1 die shows 1: prob=COMBIN(4,1)/6^4 .... Jerry Al wrote: Column a is a list of possible results that can be generated by the use of 4 six sided dice together ranging from 4-24. What I want to calculate in column B is the number of combinations that the dice can produce that would total to any given result. How do I do this? Can I do this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate probability
It is less obvious than my previous suggestion, but you could also count the
number of ways to get a given total from n dice (numerators to divide by 6^n for the probability) from the numerators for rolling n-1 dice. In A1:A6 enter the number 1 -- these are the numerators for rolling 1 die In B1 enter the formula =SUM(OFFSET(A1,MAX(-5,1-ROW()),0,MIN(6,ROW()),1)) Copy B1 and paste into B2:B11 -- these are the numerators for rolling 2 dice Copy B1 and paste into C1:C16 -- these are the numerators for rolling 3 dice ... this implements the formula given by http://www.research.att.com/cgi-bin/...i?Anum=A063260 Jerry "Jerry W. Lewis" wrote: Becase of the multiple modes by which a total can be obtained, this doesn't lend itself to a simple expression (unlike the 2-dice problem). If I wanted to calculate the results in a hurry without thinking about the combinatorics, then I would use helper columns as follows: In C1:F1 enter the number 1 In C2 enter the formula =IF(SUM(D2:$F2)=COUNT(D2:$F2),IF(C1=6,1,C1+1),C1) Copy C2 and Paste into D2:E2 In F2 enter the formula =IF(F1=6,1,F1+1) Copy C2:F2 and Paste into C3:F1296 In G2 enter the formula =SUM(C1:F1) Copy G2 and Paste in to G3:G1296 Now the probability of getting a total of n across the 4 dice can be calculated as =COUNTIF($G$1:$G1296,n)/6^4 Jerry "Al" wrote: So how do I turn all of that into something reasonably neat use in my caculations for column B? "Jerry W. Lewis" wrote: Assuming fair dice 4 can occur only if each die shows a 1: prob=1/6^4 5 can occur if 1 die shows 2 and the rest show 1: prob=COMBIN(4,1)/6^4 6 can occur if 1 die shows 3 and the rest show 1: prob=COMBIN(4,1)/6^4 or if 2 dice show 2 and the rest show 1: prob=COMBIN(4,2)/6^4 (add the two probabilities) 7 can occur if 1 die shows 4 and the rest show 1: prob=COMBIN(4,1)/6^4 or if 1 die shows 3, 1 die shows 2 and the rest show 1: prob=MULTINOMIAL(1,1,2)/6^4 or if 3 dice show 2 and 1 die shows 1: prob=COMBIN(4,1)/6^4 .... Jerry Al wrote: Column a is a list of possible results that can be generated by the use of 4 six sided dice together ranging from 4-24. What I want to calculate in column B is the number of combinations that the dice can produce that would total to any given result. How do I do this? Can I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to calculate a cube root | New Users to Excel | |||
How do I calculate hours in Excel | New Users to Excel | |||
Spreadsheet Won't Calculate | Excel Discussion (Misc queries) | |||
help with sumif to calculate column | Excel Discussion (Misc queries) | |||
Not able to calculate. | Excel Worksheet Functions |