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 threw together a *[EDIT: now somewhat less]* simple interactive widget using Jupyter with [``dyce``](https://posita.github.io/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][1]][2]

<s>This models a TH and DC that are both determined by d20 roll each round (which I assumed because pools of d6s were eligible to be put in play for both attack and defense). If that's wrong (i.e., only the PC rolls, and it's against a static DC if the PC is attacking or a static TH if the PC is defending), it shouldn't be too difficult to correct for.</s> *EDIT: This* now *models 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 vs. PC (supporting symmetrically-negating crit hits/misses). The code is a little unwieldy, but the critical functions are [``expected_dmg_frm_rnd_pc_attacks``](https://gist.github.com/posita/c479522963f34c149e22871667b5cc03/826ab11d9abc43a35aaddea761930a967eb35dfa#file-calc-py-L19-L47), [``expected_dmg_frm_rnd_pc_defends``](https://gist.github.com/posita/c479522963f34c149e22871667b5cc03/826ab11d9abc43a35aaddea761930a967eb35dfa#file-calc-py-L50-L78), and [``expected_dmg_frm_rnd_pc_v_pc``](https://gist.github.com/posita/c479522963f34c149e22871667b5cc03/826ab11d9abc43a35aaddea761930a967eb35dfa#file-calc-py-L81-L115).*


[``anydyce``](https://posita.github.io/anydyce)³ is used to to generate "burst" graphs. You can play around with it in Binder: [![Launch Binder](https://mybinder.org/badge_logo.svg)][2] [[source GitHub Gist](https://gist.github.com/posita/c479522963f34c149e22871667b5cc03/826ab11d9abc43a35aaddea761930a967eb35dfa)]⁴

A new instance may take awhile to launch if that link hasn't been followed in awhile. Binder will delete a launched instance after a period of inactivity, so download any work you want to save.

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.

² The prior versions of the [gist](https://gist.github.com/posita/c479522963f34c149e22871667b5cc03/c127cd81a6556e20cfe3faaa82e6a7491e70b602) and [binder](https://mybinder.org/v2/gist/posita/c479522963f34c149e22871667b5cc03/c127cd81a6556e20cfe3faaa82e6a7491e70b602?labpath=_dpr.ipynb) are available, if helpful.

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

⁴ While Gist, Jupyter Lab, and Binder seem like pretty durable projects, who knows if or when any will disappear or change? The technically inclined can [download a zip file of the Gist](https://gist.github.com/posita/c479522963f34c149e22871667b5cc03/archive/826ab11d9abc43a35aaddea761930a967eb35dfa.zip) and use it locally. To do that, you'll need a working installation of Jupyter Lab. There are [several ways](https://jupyterlab.readthedocs.io/en/stable/getting_started/installation.html) to do this, but each is fairly technical. I run Jupyter Lab via `pip` in a Python virtual environment because I'm comfortable with that workflow. For someone not familiar with those tools, the easiest way (while still not super easy) is probably [via Docker](https://jupyter-docker-stacks.readthedocs.io/en/latest/#quick-start).

For example, I was able to run the notebook 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``. There is an extra step that Binder automates, which is installing some additional dependencies. You can actually address this manually inside the notebook itself by adding new cell before the final one with the following contents:

``` sh
%%sh
# Installs the notebook dependencies
pip install --requirement requirements.txt
```

[![enter image description here][3]][3]

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](https://jupyter-notebook.readthedocs.io/en/stable/security.html#security-in-the-jupyter-notebook-server). 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.


  [1]: https://i.sstatic.net/wM7vj.png
  [2]: https://mybinder.org/v2/gist/posita/c479522963f34c149e22871667b5cc03/826ab11d9abc43a35aaddea761930a967eb35dfa?labpath=_dpr.ipynb
  [3]: https://i.sstatic.net/yhQYH.png