I think you're on the right track. If you're doing this in a spreadsheet,
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:
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
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: [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.