Edge spreadsheet, Spreadsheet to work out when to spend edge Options

[FuelDrop]

Spreadsheet

So quick explanation: this is a spreadsheet that shows average number of hits on a roll of x dice, then shows the expected average result if you spend edge before rolling, after rolling to get rerolls, or after rolling to roll extra dice. It factors in the effects of exploding dice, and covers edge 1 through edge 8.

Your edge rating is the number on the top row, your dicepool before edge is the first column. The 0 edge column is the average roll without spending edge.

If a number is highlighted green you want to spend edge for extra dice (tab will tell you if this is before or after rolling). If the number is highlighted red then you should use edge to reroll, barring weighted dice.

This sheet does not factor in the odds of a chain exploding die (a die roll that rolls several 6's in a row), but whenever reroll and extra dice have statistically identical results I have recommended rolling more dice due to the outside chance of a secondary explosion.

As I understand it Edge in 6th edition works differently, hence I have set this as a 4E topic since that is the ruleset edge works like this in. Hope you find this helpful.

[Lionesque]

Very nice of you to share, especially as it illustrates the no. 1 problem with Shadowrun (any edition): in a game with rainbow-colored mohawks, characters with cyberarms that go pewpewpew and and immensely powerful dragons, we turn to...Excel? WTH?

[Tecumseh]

Here's a quick rule-of-thumb about when to pre-roll Edge vs. when to use it to reroll dice that weren't hits.

Multiply your Edge by 18/7 (that's 18 divided by 7). That's the cutoff. If your dice pool is lower than that, pre-roll Edge. If your dice pool is larger than that, use Edge to reroll successes.

For example, say your Edge is 5. 5 * 18 / 7 = 12.86. So you should pre-roll Edge for dice pools of 12 or less and use Edge to reroll non-hits on dice pools of 13 or more.

--------------------------------------------------------------------------------------------

@FuelDrop I don't think the equation on the "Edge before rolling" tab is quite right.

Mathwise, we want to find the point where rerolls average equal the average of pre-rolling with exploding dice.

First, let's calculate the value of a die in a dice pool where you're going to reroll:
Rerolled Pool Die = 1/3 initial hits + (2/3 rerolled dice * 1/3 rerolled hits) = 3/9+2/9 = 5/9

Next, let's calculate the value of a die in a dice pool with exploding dice. This is trickier because we get into infinite polynomials, which is to say that you could potentially keep rerolling the exploding die forever. I can write it out but the the short version is that pre-rolling sets the value of all the dice pool dice (including the Edge you added) to 2/5 (up from 1/3).

Now, with X dice and Y Edge, we want to find the point where the reroll pool (X*5/9) equals the value of the pre-Edge pool (X*2/5 + Y*2/5). We need to multiply the denominators so that they're all consistent, which will set denoninators to 45. So 5/9 becomes 25/45, and 2/5 becomes 18/45.

Reroll 25/45 * X = Pre-Edge 18/45 * X + 18/45 * Y

...after we move the Xs over to one side, becomes...

7/45 * X = 18/45 * Y

... becomes ...

7X = 18Y

... becomes...

X = 18/7Y

Insert an Edge attribute of 4 into that as Y, and we get 10 2/7 for X, so at 10 dice and fewer then pre-Edge is better, and at 11 dice then rerolling is better.