Hi !

I've been toying too much with excel, of late, and I have trouble finding a solution to a simple problem : calculating the odds (%) of glitches happening per dice pool number.

Glitches, if I'm not mistaken, happen if :

• Exactly half or more than half the number of dice rolled turn out to be 1's.
• If the number of dice is an odd number (3, 5, 7, 9, ... 37, etc.), then you divide the DP by 2 and round up. This means a DP of 3 must have 2 dice show up as 1's for a glitch to occur.

I'm trying to represent this on a table. If my previous calculations are exact (which I now doubt, given my failing math skills...), I should already have the odds for all DPs from 1 to 99, and for all threshold from 0 (failure) to 41 (which makes for a fraggin' huge table). Given that rows represent DP from 1 to 99, what I want is to add a first column called "Glitches" that gives me the odd of glitching on any DP from 1 to 99.

How can I translate this in a formula using excel ?

(As an afterthought, what would be the formula for "critical glitches", given they stem from a different condition ?)

chance for the die to roll a 1= 1/6
chance for the die to roll anything else= 5/6
chance for the die to roll neither 1,5,6= 1/2

glitch=(1/6)^(ROUNDUP(d/2)) * (5/6)^(ROUNDDOWN(d/2))
crit glitch=(1/6)^(ROUNDUP(d/2)) * (1/2)^(ROUNDDOWN(d/2))

that's not complete. that's only a single case with the highest possibility...but might be a start.

That only accounts for rolling the minimum number of 1s and only one combination of that.

The formula is tricky in Excel because the number of terms depends on the size of the dice pool. Each term goes like this:

G(n) = Probability of glitch with n 1s
d = size of dice pool
n = no. of 1s
FACT(x) = 1 * 2 * 3 * ... * x

G(n) = (1/6)^n * (5/6)^(d-n) * FACT(d)/(FACT(d-n)*FACT(n))

So for a dice pool of 5 you need to run that formula for d = 5 and n = 3, 4 and 5, then add the three together.
For critical glitches, as Makki said, change the 5/6 with 1/2 but remember that the glitch formula also accounts for critical glitches.

i know. i started and then ran out of time and passion
i did it once before, but no clue where that excel file is

=BINOMDIST(ROUNDUP(X/2,0),X,1/6,FALSE)

Where X is equal to the number of dice rolled; can be replaced with a cell reference (suggested).


Edit: Again, this only accounts for the probability of rolling the minimum number of 1's necessary to glitch. I did this before, I just need to figure out how (I might have used this formula in a chart to calculate totals).\

Edit: Explanation of the formula used, & the difficulties I am having making it work for this calculation.

BINOMDIST(number_s, trials, probability_s, cumulative)

number_s = number of successes
trials = number of trials
probability_s = chance of success
cumulative = False calculates chance of number_s, True calculates chance of less than number_s

Example: Dice Roll
number_s = ROUNDUP(X/2,0); one-half X (number of dice rolled), rounded up to a whole integer.
trials = X; the number of dice rolled
probability_s = 1/6; 16.66% chance of rolling a 1, checked X times (once for each die rolled)
cumulative = false; calculate chance of exactly number_s 1's (if true, would instead calculate the chance of at most number_s 1's)



What I need is to set number_s to "or more"

>=X is not a valid formula
X>=Y requires an external reference & checks if X is greater than or equal to Y
If functions require an external reference or create a self-referencing loop

I did some quick searching of google & am asking around if anyone knows how to set X or more as a number, rather than a check. If you figure this out, it would allow you to use this single formula to quickly check the exact chance of glitching for any dice pool. Placing this & a copy for calculating the chance of 1 or more successes in an IF function would allow you to calculate the chance of a glitch vs. critical glitch.

Figured it out. By taking the value of a TRUE cumulative minus the value of a FALSE cumulative, we are given the chance of not rolling a glitch. Subtracting this from 1 will give us what we are looking for. Alternatively, you might be able to achieve the same result with proper use of the OR function in number_s, but it would be tricky & I'm not entirely certain it would work (haven't played around with it much yet).

The formula you are looking for is:
=1-(BINOMDIST(ROUNDUP(X/2,0),X,1/6,TRUE)-BINOMDIST(ROUNDUP(X/2,0),X,1/6,FALSE))
Where X is equal to the number of dice rolled; can be replaced with a cell reference (suggested).

Again, properly placing this and a copy for calculating the chance of one or more success (using an AND function, I think) inside an IF function should tell you the chance of a Critical Glitch. Subtracting that value from the formula above (which gives the total chance of a glitch) will give you the chance of a critical glitch and the chance of a normal glitch separately.

Edit: Actually, by using an AND function for calculating critical glitches, we want the chance of not getting one or more success. Shouldn't be hard - just use the BINOMDIST function to calculate the chance of getting exactly 0 successes (0 number_s, 1/3 probability_s, FALSE cumulative).