Calculating SR4 Probabilities in Excel?, A little help, please... 
Calculating SR4 Probabilities in Excel?, A little help, please... 
Nov 13 2008, 07:23 PM
Post
#1


Moving Target Group: Members Posts: 115 Joined: 27March 08 Member No.: 15,819 
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) 


Nov 13 2008, 07:32 PM
Post
#2


Moving Target Group: Members Posts: 656 Joined: 18January 06 From: Leesburg, Virginia, USA Member No.: 8,177 
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,1badglitch,TRUE)*(1hit)^dice Any Glitch =BINOMDIST(dice  CEILING(dice/2, 1),dice,1bad,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 


Nov 13 2008, 07:58 PM
Post
#3


Moving Target Group: Members Posts: 129 Joined: 28October 07 Member No.: 13,928 



Nov 13 2008, 08:01 PM
Post
#4


Awakened Asset Group: Members Posts: 4,464 Joined: 9April 05 From: AGS, North German League Member No.: 7,309 
Are you aware of Feshy´s diceroller with statistics function?



Nov 13 2008, 08:05 PM
Post
#5


Running Target Group: Members Posts: 1,263 Joined: 4March 08 From: Blighty Member No.: 15,736 
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 preroll adding. 


Nov 13 2008, 08:37 PM
Post
#6


Moving Target Group: Members Posts: 656 Joined: 18January 06 From: Leesburg, Virginia, USA Member No.: 8,177 
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 preroll 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.) 


Nov 13 2008, 10:14 PM
Post
#7


Moving Target Group: Members Posts: 129 Joined: 28October 07 Member No.: 13,928 
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? 


Nov 13 2008, 10:23 PM
Post
#8


Moving Target Group: Members Posts: 656 Joined: 18January 06 From: Leesburg, Virginia, USA Member No.: 8,177 
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. 


LoFi Version  Time is now: 20th February 2017  06:09 PM 
Topps, Inc has sole ownership of the names, logo, artwork, marks, photographs, sounds, audio, video and/or any proprietary material used in connection with the game Shadowrun. Topps, Inc has granted permission to the Dumpshock Forums to use such names, logos, artwork, marks and/or any proprietary materials for promotional and informational purposes on its website but does not endorse, and is not affiliated with the Dumpshock Forums in any official capacity whatsoever.