Calculating SR4 Probabilities in Excel?, A little help, please... Options

[TeaTime]

Initiated Mathemagicians and miscellaneous Numberatti, I could use a hand with an excel formuli (I've got a one in Knowledge: Calculating Odds, and I've been glitching on this extended test).

I'm trying to find the excel formula for calculating the percent probability of rolling a certain number of successes from a particular number of dice. I'd like to build a table with Number of Dice Rolled along the top, and Number of Successes along the side, with percent chances at the intersect.

Later, I'd like to do something similar with Edge, but for the moment, getting this formula is a good first step.

X= Total Number of Dice Rolled
Y= Number of Successes (5 or 6)

[JoelHalpern]

The Excel formula for getting exactly "gross" hits when rolling "dice" cubes is:
=BINOMDIST(gross,dice,hit, FALSE)
where hit is 1/3 either as a value or in a cell.

If you replace FALSE with TRUE I believe you will get cumulative, but you need to check that as to which way it is accumulating.

For other statistical values,
mean on N dice is of course N/3
The standard deviation of that mean is sqrt(2*N)/3

In terms of the effect on the mean, edge multiplies the mean by 1.2 (6/5).
I can reconstruct what it does to the standard deviation, but just take it as :increased.

Glitch Probabilities:
Critical Glitch =BINOMDIST(dice - CEILING(dice/2, 1),dice,1-badglitch,TRUE)*(1-hit)^dice
Any Glitch =BINOMDIST(dice - CEILING(dice/2, 1),dice,1-bad,TRUE)

Where bad is 1/6, badglitch = 1/4 (chance of 1 out of the 1..4 miss range) and hit is 1/3.

I hope that this is helpful.
Joel M. Halpern

[Bobson]

In terms of the effect on the mean, edge multiplies the mean by 1.2 (6/5).

Is that using edge to reroll failures, or adding edge dice (which means 6's explode)?

[Ryu]

Are you aware of Feshy´s diceroller with statistics function?

[Heath Robinson]

For other statistical values,
mean on N dice is of course N/3
The standard deviation of that mean is sqrt(2*N)/3

In terms of the effect on the mean, edge multiplies the mean by 1.2 (6/5).
I can reconstruct what it does to the standard deviation, but just take it as :increased.

And for all intents and purposes, the Expectation is equal to the Mean.

If you act as if you commit to rerolling before your roll, then your probability per dice of achieving a hit rises to 5/9 (ie 1/3 + (2/3 * 1/3)). Which you can use to calculate your breakpoint for rerolling becoming more efficient than pre-roll adding.

[JoelHalpern]

And for all intents and purposes, the Expectation is equal to the Mean.

If you act as if you commit to rerolling before your roll, then your probability per dice of achieving a hit rises to 5/9 (ie 1/3 + (2/3 * 1/3)). Which you can use to calculate your breakpoint for rerolling becoming more efficient than pre-roll adding.

Yes, the two different ways of using edge give very different results. And the one Heath describes is, for most ranges, actually better.
If you use expoding dice edge, then for N dice and M edge you get (N+M)*0.4 as the expected number of hits
(6/5 of 1/3 of N+M).
As Heath says, with rerolling failed dice, you get N*5/9.
(You can do the math to see in which ranges which is better, but then remember that if you get enough initial hits you don't need to bother with the edge usage.)

[Bobson]

Yes, the two different ways of using edge give very different results. And the one Heath describes is, for most ranges, actually better.
If you use expoding dice edge, then for N dice and M edge you get (N+M)*0.4 as the expected number of hits
(6/5 of 1/3 of N+M).
As Heath says, with rerolling failed dice, you get N*5/9.
(You can do the math to see in which ranges which is better, but then remember that if you get enough initial hits you don't need to bother with the edge usage.)

If rolling with edge included adds 40% or more to your dice pool (i.e. 4 or more edge when you have 10 dice to begin with), then if you're going to spend edge either way, it's always better to spend the edge up front. On the other hand, the whole point of being able to reroll failures is that you don't have to spend the edge, so it goes back to being a judgement call. How much chance of success is the chance of preserving your Edge worth?

[JoelHalpern]

If rolling with edge included adds 40% or more to your dice pool (i.e. 4 or more edge when you have 10 dice to begin with), then if you're going to spend edge either way, it's always better to spend the edge up front. On the other hand, the whole point of being able to reroll failures is that you don't have to spend the edge, so it goes back to being a judgement call. How much chance of success is the chance of preserving your Edge worth?

Okay, the rest of the calculation.
In a lot of cases N*5/9 is going to be more than (N+M)*0.4.
Multplying through and subtracting. the condition is that
N*7/45 > M*0.4
Remember that N is your total dice pool, and M is your edge.
Obviously, for small pools, adding edge dice and exploding is better than rerolling failures. But, if we expand the formula, we find that rerolling failures is better if
N*7 > M*18

Now, if you have 3 edge dice, that means you are better off rerolling failing dice if you have 8 or more dice in the pool.
Even with 5 edge dice, you are better off rerolling failures if your pool is 13 or more dice.
(And that is ignoring the beneift of not using the edge if you roll well in the first place, as well as some other second order effects that make rerolling failed dice somewhat better.)

Yours,
Joel

PS: The big thing to remember about Shadowrun dice is the standard deviation. Unless the GM allows the rule of 4, there is tremendous variation in the results of rolls. That appears to be intentional, and may well be a good thing.