IPB

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Calculating SR4 Probabilities in Excel?, A little help, please...
TeaTime
post Nov 13 2008, 07:23 PM
Post #1


Moving Target
**

Group: Members
Posts: 115
Joined: 27-March 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)
Go to the top of the page
 
+Quote Post
JoelHalpern
post Nov 13 2008, 07:32 PM
Post #2


Moving Target
**

Group: Members
Posts: 655
Joined: 18-January 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,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
Go to the top of the page
 
+Quote Post
Bobson
post Nov 13 2008, 07:58 PM
Post #3


Moving Target
**

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



QUOTE (JoelHalpern @ Nov 13 2008, 02:32 PM) *
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)?
Go to the top of the page
 
+Quote Post
Ryu
post Nov 13 2008, 08:01 PM
Post #4


Awakened Asset
********

Group: Members
Posts: 4,390
Joined: 9-April 05
From: AGS, North German League
Member No.: 7,309



Are you aware of Feshy┬┤s diceroller with statistics function?
Go to the top of the page
 
+Quote Post
Heath Robinson
post Nov 13 2008, 08:05 PM
Post #5


Running Target
***

Group: Members
Posts: 1,263
Joined: 4-March 08
From: Blighty
Member No.: 15,736



QUOTE (JoelHalpern @ Nov 13 2008, 07:32 PM) *
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.
Go to the top of the page
 
+Quote Post
JoelHalpern
post Nov 13 2008, 08:37 PM
Post #6


Moving Target
**

Group: Members
Posts: 655
Joined: 18-January 06
From: Leesburg, Virginia, USA
Member No.: 8,177



QUOTE (Heath Robinson @ Nov 13 2008, 03:05 PM) *
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.)
Go to the top of the page
 
+Quote Post
Bobson
post Nov 13 2008, 10:14 PM
Post #7


Moving Target
**

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



QUOTE (JoelHalpern @ Nov 13 2008, 03:37 PM) *
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?
Go to the top of the page
 
+Quote Post
JoelHalpern
post Nov 13 2008, 10:23 PM
Post #8


Moving Target
**

Group: Members
Posts: 655
Joined: 18-January 06
From: Leesburg, Virginia, USA
Member No.: 8,177



QUOTE (Bobson @ Nov 13 2008, 05:14 PM) *
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.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic

 



RSS Lo-Fi Version Time is now: 25th October 2014 - 07:05 AM

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.