Results 1 to 8 of 8
Like Tree1Thanks
  • 1 Post By Plato

Math Help - Dice Probability in an Excel Spreadsheet

  1. #1
    Newbie
    Joined
    Jul 2012
    From
    UK
    Posts
    2

    Dice Probability in an Excel Spreadsheet

    A person throws a normal 6-sided dice a certian number of times, and their score is the highest value they throw. What is the probability of them getting a certain score?

    I need to calculate the probability of getting all the scores 1 to 6, for up to about 5 throws. I can of course do this manually in a spreadsheet by entering an individual formula into every cell, but is there a more general formula I could use? I've tried looking at my notes but although I generally understand probabilities and permutations/combinations, I'm pretty stuck...

    Any help would be really appreciated!
    Follow Math Help Forum on Facebook and Google+

  2. #2
    mfb
    mfb is offline
    Junior Member
    Joined
    Jun 2012
    From
    Germany
    Posts
    72
    Thanks
    5

    Re: Dice Probability in an Excel Spreadsheet

    What about this?
    - calculate the probability to have no 6.
    -- this gives you the probability to have at least one 6.
    - calculate the probability to have no 6 and no 5.
    -- the difference between both gives you the probability to have at least one 5, but no 6.
    continue the pattern for all other values.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    MHF Contributor

    Joined
    Aug 2006
    Posts
    18,607
    Thanks
    1574
    Awards
    1

    Re: Dice Probability in an Excel Spreadsheet

    Quote Originally Posted by BeccyM View Post
    A person throws a normal 6-sided dice a certian number of times, and their score is the highest value they throw. What is the probability of them getting a certain score? I need to calculate the probability of getting all the scores 1 to 6, for up to about 5 throws.
    I can of course do this manually in a spreadsheet by entering an individual formula into every cell, but is there a more general formula I could use?
    Frankly I puzzled by the statement in blue. Because of the particular rules of this game, I doubt there is a neatly compact solution. This is a counting problem. As such this solution may not be any different from what you have in mind with a spreadsheet.

    Say we roll the die N times. Let k=1,~2,\cdots,~6.
    Define a finite sequence d_1=1 and for k\ge 2,~d_k={k^N} - \sum\limits_{j = 1}^{k - 1} {{d_j}}.
    That is a sequence of the number of ways of winning for each k.

    So for example, the probability of four winning is \frac{d_4}{6^N}.
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Super Member
    Joined
    Mar 2008
    Posts
    934
    Thanks
    33
    Awards
    1

    Re: Dice Probability in an Excel Spreadsheet

    Here is another approach.

    Let's say the die is rolled n times and the maximum number is X. Consider a single roll; the probability that the score is less than or equal to x is x / 6. So the probability that all n rolls are less than or equal to x is (x / 6)^n, i.e.
    \Pr(X \leq x) = (x / 6)^n.

    Now you can use \Pr(X = x) = \Pr(X \le x) - \Pr(X \le x-1).
    Last edited by awkward; July 1st 2012 at 11:29 AM.
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Newbie
    Joined
    Jun 2012
    From
    Pakistan
    Posts
    8

    Re: Dice Probability in an Excel Spreadsheet

    Nice stuff for my research writing.Wonderful post! This is very useful to many readers like me
    Health articles
    . Being a student, I am requiring myself to read articles more often and your writing just caught my interest.
    free legal help online

    Thank you so much!
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Super Member
    Joined
    Jun 2012
    From
    AZ
    Posts
    616
    Thanks
    97

    Re: Dice Probability in an Excel Spreadsheet

    Think of it this way: If X is the score you obtain, then

    P(X=1) = (\frac{1}{6})^5, i.e. you need to roll a 6 five times in a row to score 1.

    To score 2, you need to roll a 1 or 2 on each die, but at least one die must be a 2. It follows that

    P(X=2) = (\frac{2}{6})^5 - (\frac{1}{6})^5

    This generalizes to

    P(X=k) = (\frac{k}{6})^5 - (\frac{k-1}{6})^5 - \cdots - (\frac{1}{6})^5

    And obviously the 5 can be replaced with any other positive integer, in order to generalize even more.
    Follow Math Help Forum on Facebook and Google+

  7. #7
    Newbie
    Joined
    Jul 2012
    From
    UK
    Posts
    2

    Re: Dice Probability in an Excel Spreadsheet

    Thank you everyone for your help!

    To: mfb,

    Yes I think it is something like that, but I can't think of a formula that I could copy and paste into every cell lol!

    To: Plato,

    So for a score of 4 in 1 throw, the formula would be

    (4^1 - (3^1 + 2^1 + 1^1) / (6^1) ???

    And of course you are right about the fact that neat compact solution is what I'm looking for anyway :/

    To: awkward, richard1234

    You seem to be saying that if "n" is the no. of throws, the probability of getting a score of 1 is (1/6)^n, and then the probability of getting a 2 is (2/6)^n - (1/6)^n and so on (if I understand correctly)

    However things seem to go horribly wrong when I enter this formula into a spreadsheet :/

    For example, the probability of getting a score of 3 with 1 throw is, according to this formula, (3/6)^1 - ((2/6)^1 + (1/6)^1)) = 0 (!)

    Also, when I repeat the calculation with other values the probabilities don't add up to 1... I think it might be to do with the fact that there are lots of different permutations of the different scores?

    *Edit* I think I might've worked it out... Very complicated it took me an hour... If anyone could check and confirm if you think I've got it right that would be much appreciated!

    Where n = no. of throws, s = score

    P(S) = Q - (the sum of all the probabilities of the scores greater than that score)

    where Q is a cumulative distribution function with the following parameters:

    Number of successes in trials = (n-1)
    No. of trials = n
    Probability of success = s/6

    My apologies if this isn't explained very clearly. Here's the spreadsheet:

    https://docs.google.com/open?id=0ByA...lg2NnR3MGpFYUk

    (I think you have to go to file>download to see the formulae)
    Last edited by BeccyM; July 3rd 2012 at 12:36 PM.
    Follow Math Help Forum on Facebook and Google+

  8. #8
    MHF Contributor

    Joined
    Aug 2006
    Posts
    18,607
    Thanks
    1574
    Awards
    1

    Re: Dice Probability in an Excel Spreadsheet

    Quote Originally Posted by BeccyM View Post
    To: Plato,
    So for a score of 4 in 1 throw, the formula would be
    (4^1 - (3^1 + 2^1 + 1^1) / (6^1) ???
    I have attached a image of the probabilities for 1 to 6 in five throws.

    BTW. Reply #4 is exactly the same as mine except I actually computed the probabilities.
    Attached Thumbnails Attached Thumbnails Dice Probability in an Excel Spreadsheet-untitled.gif  
    Last edited by Plato; July 3rd 2012 at 02:00 PM.
    Thanks from BeccyM
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Excel Spreadsheet Problem
    Posted in the Math Topics Forum
    Replies: 1
    Last Post: June 16th 2011, 08:14 AM
  2. Help w/ Excel spreadsheet & probability
    Posted in the Statistics Forum
    Replies: 1
    Last Post: May 19th 2010, 09:51 PM
  3. dice probability
    Posted in the Statistics Forum
    Replies: 6
    Last Post: January 16th 2010, 10:58 AM
  4. Any good probability (in excel) learning resources?
    Posted in the Advanced Statistics Forum
    Replies: 0
    Last Post: August 7th 2009, 03:53 PM
  5. dice probability
    Posted in the Statistics Forum
    Replies: 1
    Last Post: October 31st 2006, 06:59 AM

Search Tags


/mathhelpforum @mathhelpforum