Home Forums General Game Design Need a Statistical Engine for… Reply To: Need a Statistical Engine for…

#88326
John D Salt
Participant

I would recommend using Python for reasons of sanity, and have just knocked up the following program which seems to do what you require; it’s only a dozen lines of actual code. Python is free, and easy to install and run.

If you really must have a spreadsheet, it should be easy enough to work out how the algorithm works from the code below or from the place I nicked it, https://owlcation.com/stem/How-to-Work-Out-Odds-Permutations-and-Combinations. The LibreOffice built-in function for calculating a factorial is called “factorial”, and I imagine it is the same in Excel.

As it’s late on a Sunday, I shall let you go without blethering on about how binomial distributions are connected to Pascal’s traingle and Sierpinski’s gasket.

All the best,

John.

########################################################################
#
# Rollem.py
#
# Calculate probabilities of s successes in n dice rolls
#
# Written by John D Salt 08 Apr 2018
#
# Based on description given at
# https://owlcation.com/stem/How-to-Work-Out-Odds-Permutations-and-Combinations
#
# This program may be shared and redistributed freely
#
########################################################################

from math import factorial

def probExactly(p, s, n):
    ''' Return probability of exactly s successes in n rolls with success probability p '''
    pf = 1.0-p # probability of failure
    nf = n-s   # number of allowable failures
    return (factorial(n)/(factorial(nf)*factorial(s))*(pf**nf)*(p**s))

def probAtLeast(p, s, n):
    ''' Return probability of at least s successes in n rolls with success probability p '''
    prob = 0.0
    x = s
    while x <= n:
        prob+=probExactly(p, x, n)
        x+=1
    return prob

def test():
    print probExactly(1./6., 3, 10), 'should be about 0.15505'
    print probAtLeast(1./6., 3, 10), 'should be about 0.22477'