I am tweaking my homebrew RPG and trying to determine various Damage Per Round through an Excel spreadsheet. Here are the details:

  • A single players always roll d20 system where 20 is a auto hit and 1 is auto miss.
  • There is no additional roll to damage. Weapons have static Damage and Rate of Fire. The way RoF works is for every point To-Hit (TH) that exceeds the defense value (DC) an additional hit is made up to the max RoF. For example, a PC rolls (+mods) a TH15 with a repeating crossbow that fires 3 bolts (RoF3). Comparing it to an NPCs static (no roll) DC14, the PC hits the them twice, once for meeting DC14, and once for exceeding it by 1 point. Had it been TH14 there would be just 1 hit, and TH18 would have yielded 3 hits because the number of bolts is capped at 3 (RoF3).
  • While a natural 20 always hits, the TH:DC ratio for RoF remains, e.g. a TH22 accompanied by a Nat20 vs a DC22 or higher would still yield a single hit regardless of RoF. I have a bonus system planned that doesn't need to be taken into account.
  • Attackers and Defenders have static positive modifiers like Accuracy (ACR) and Agility (AGL) that are added to TH and DC respectively.
  • Attackers and Defenders can further modify their TH and DC with a limited pool of d6s; higher character level increases the size of the pool and how many d6s they may add from it to modify their TH or DC. I believe this is is simply the average 3.5 of a d6 multiplied by #d6s, though that may not be right.
  • Defender Armor reduces each hit (e.g. 2 hits at 2 DMG each vs Armor 1 would do 1 DMG each for a total of 2 DMG). Armor may reduce DMG to zero, but never into the negative.

Below is my formula thus far, which gets me to the success rate of an RoF1. I am not showing any static modifications (AGL, ACR, etc) as they can get lumped into the TH and DC, while I am showing the d6 modifier from the characters' pool because of the random nature of bonus d6s:

$$ \left(21-\frac{(DC+3.5\#d6)-(TH+3.5\#d6)}{20}\right)\times100 $$

What I can't figure out from here is how to include atuo-hit on 20 and auto-miss on 1 (the above equation can yield 100% chance, which shouldn't happen), DMG, RoF, and Armor.

  • \$\begingroup\$ I don't understand how your system works. In the case of "a TH15 vs DC14 with a ROF3 would mean 2 hits" does this mean you're rolling two d20s (plus bonuses)? Or do you roll to hit once and then apply damage twice? Can you run us through a single turn with specific numbers for an example? \$\endgroup\$
    – Laurel
    Jan 24, 2022 at 20:07
  • \$\begingroup\$ @Laurel It looks like there was one attack roll for the weapon, which got a 15. Since that's one point over the DC of 14, up to 2 attacks could hit, and since the crossbow is RoF 3, those two hit. Had it been RoF 1, only one attack could have hit despite the 15, since the maximum hits is 1. Had it been RoF 10, still only two could have hit. However, an 18 would have had only 3 hits, since the maximum is 3. \$\endgroup\$
    – Phoenices
    Jan 24, 2022 at 21:10
  • \$\begingroup\$ @Laurel, I added some clarity to my post., but yes, in the example you roll to hit once to get your TH15 and apply damage twice (+1 hit because TH15 is 1 more than DC14). \$\endgroup\$
    – MLee17
    Jan 24, 2022 at 21:10
  • \$\begingroup\$ Oh and, Welcome to RPG.SE! Take the tour if you haven't already and see the help center or ask us here in the comments (use @ to ping someone) if you need more guidance. Good Luck and Happy Gaming! \$\endgroup\$
    – Someone_Evil
    Jan 24, 2022 at 22:15
  • 1
    \$\begingroup\$ @illustro, revised to answer your questions. Thanks. \$\endgroup\$
    – MLee17
    Jan 24, 2022 at 23:41

2 Answers 2


I think you're on the right track. If you're doing this in a spreadsheet, MIN, MAX, and probably IF are going to be your friends for enforcing cutoffs and boundaries (like non-negative damage, auto hits/misses, etc.).

I'm guessing what you're trying to get a feel for is not (just) the average expected damage per round, but the distribution. That might be more challenging to do in a spreadsheet (although totally possible). My Spreadsheet Fu is lacking, so others will likely be able to provide more guidance as to specific strategies.

That being said, I put together an interactive widget using Jupyter with dyce¹ that might help. Assuming I understand your mechanic (and got my math and my widgets wired up right), the amount of damage a PC can expect to land on a single round with a TH mod of -2, a DC target of 11, 2d6 on attack, 1d6 on defense, a DMG of 3, Armor of 1, and an RoF of 6 is:

Widget Screenshot

It is capable of modeling three scenarios: a PC attacking (supporting crit hits on 20/misses on 1 against the NPC); a PC defending (supporting crit misses on 20/hits on 1 against the PC); and PC v. PC (supporting symmetrically-negating crit hits/misses). The code is a little unwieldy, but the critical functions are expected_dmg_frm_rnd_pc_attacks, expected_dmg_frm_rnd_pc_defends, and expected_dmg_frm_rnd_pc_v_pc.

One limitation about this implementation is that it does not show hits separately from DMG. That might be important if, for example, contacting an opponent was situationally advantageous, even if it didn't result in harm. (For example, one particularly sharp-eyed PC spots a desired target in a crowd that the rest of the party can't easily see and lobs a couple vials of talc or pies or whatever to "paint" or "mark" it.) That's easy to work in here by creating an artificial spread between DMG and Armor so you can see the number of hits while ignoring any damage. As a side note, you could compute max(0, dmg - armor) outside of the model and just have one control for that, if you wanted.

This model also assumes that Armor is not depleted by hits or DMG (as does your mechanic, I believe). Modeling such a thing would be possible, but more complicated, and may not be worth the extra accounting at the table, especially if it negatively impacts the pace of the game.

anydyce² is used to to generate "burst" graphs. You can play around with it in your browser: Try dyce [source

I'm hoping that even if you don't speak Python, the above is accessible enough to either get you where you want to go calc-wise, or give you enough inspiration to modify your spreadsheet to get it to do what you want. Like I said, I think you're close.

¹ dyce is my Python dice probability library.

² anydyce is my visualization layer for dyce meant as a rough stand-in for AnyDice.

³ While GitHub and JupyterLab/JupyterLite seem like pretty durable projects, who knows if or when any will disappear or change? You can download a zip file of the repository and use it locally. To do that, you'll need a working installation of Jupyter Lab (or at least Jupyter).

The "easiest" way that I've found is via SageMath. SageMath a fairly powerful platform roughly analogous to Mathematica. It is a massive collection of various open source science and math packages that are beyond overkill for this notebook. However, convenient installers exist for MacOS and Windows. Note that SageMath does not yet default to Jupyter Lab (only Jupyter), so the interface is slightly different.

There are other ways, but each is fairly technical. For example, I am able to run the notebook via Docker by downloading and unzipping the aforementioned Gist zip into /tmp/dpr and then running docker run --rm --publish 8888:8888 --volume '/tmp/dpr:/home/jovyan/work' jupyter/scipy-notebook.

Binder automates installing dependencies. I added a cell to the notebook to handle that in other environments.

At the risk of finger wagging, I offer a word of caution: It's probably a good idea to avoid running downloaded scripts without inspecting and understanding them. In this case, I would examine the contents of the zip file to make sure they reflect your intention. It's also worth understanding that Jupyter is a complete Python environment that works by spooling up a local web server and offering users access to that environment. As such, it has its own security limitations. This is especially relevant if you're running it on a host connected to the internet.

Make your own decisions, of course, but sometimes reminders of risks are useful.

  • \$\begingroup\$ FWIW, I haven't spent a ton of time thinking about how to optimize that. In its current form, it will definitely take longer to regenerate the graph (sometimes a few seconds) as the number of d6s increases on either side. \$\endgroup\$
    – posita
    Jan 25, 2022 at 3:34
  • \$\begingroup\$ Thank you so much. Very cool implementation. I've edited my post again for clarity. A PC would always roll (d20s and d6s) for TH and DC, while a NPC's are static (but the GM may still add d6s from a limited pool). I understand PvP vs PvE would be different results, but I'm assuming that PvE is more common ingame. \$\endgroup\$
    – MLee17
    Jan 25, 2022 at 13:12
  • \$\begingroup\$ Does that mean that if the PC crits on defense, 1 would be an automatic hit for the NPC attacker and 20 would be an automatic miss, regardless of the NPC's TH? \$\endgroup\$
    – posita
    Jan 25, 2022 at 14:43
  • \$\begingroup\$ That had not crossed my mind, but yes, that seems ideal. Question is PC vs PC and both get Nat20s. I think the crits would cancel and it would revert back to TH vs DC. \$\endgroup\$
    – MLee17
    Jan 25, 2022 at 15:37
  • \$\begingroup\$ Hopefully my latest edit captures your sentiment. I think what you'll find is that having a target number doesn't deviate too much from an average for a roll on the other side, but it's more meaningful to see it and play around with it before drawing your own conclusions. \$\endgroup\$
    – posita
    Jan 25, 2022 at 16:05

Let \$N\$ be a random variable representing the number of hits and \$d\$ be the damage each hit does.1 It follows that the damage per round \$D=N\times d\$.

Now \$N\$ depends on 2 other random variables:


$$D=d\times\begin{cases} 0, & \text{if }H=1 \\ \max \left(0, \max \left(\begin{cases}1, &\text{if }H=20 \\ 0,&\text{otherwise }\end{cases},\min \left(rof, H-T\right)\right)-arm\right), & \text{otherwise}\\ \end{cases}$$

where \$rof\$ is the RoF and \$arm\$ is the armour.



where \$dc\$ is the defence value, \$agl\$ is the agility bonus, \$acc\$ is the accuracy bonus and \$N\$ is the result of the d6's. Specifically:


Where \$def\$ is the number of d6 the defender rolls and \$att\$ is the number rolled by the attacker.

Putting all this together gives:

$$D=d\times\begin{cases} 0, & \text{if }H=1 \\ \max \left(0, \max \left(\begin{cases}1, &\text{if }H=20 \\ 0,&\text{otherwise }\end{cases},\min \left(rof, H-\left(dc+agl-acc+(def-att)\text{d}6\right)\right)\right)-arm\right), & \text{otherwise}\\ \end{cases}$$

Here is an anydice implementation.

1We'll use the convention that capital letters represent random variables and lower case represent constants - albeit constants that we might not know.

  • \$\begingroup\$ Thank you so much. There is a lot to unpack here, but I'm going to enjoy inputting it and trying it out. What happens to the output if T ends up as a negative if attack modifiers are greater than defense modifiers? \$\endgroup\$
    – MLee17
    Jan 25, 2022 at 13:23
  • \$\begingroup\$ @MLee17 nothing in particular. Unless you roll a 1 you get as many hits as H is above T up to rof. \$\endgroup\$
    – Dale M
    Jan 25, 2022 at 21:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .