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 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 one can expect to land on a single round with a TH mod of -2, a DC mod of +1, 2d6 on attack, 1d6 on defense, a DMG of 3, Armor of 1, and an RoF of 6 is:

[![Widget Screenshot][1]][2]

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.

[``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]

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.

The source code is available via [this GitHub Gist](https://gist.github.com/posita/c479522963f34c149e22871667b5cc03), with the core mechanic implemented in the [`expected_dmg_from_round ` function](https://gist.github.com/posita/c479522963f34c149e22871667b5cc03#file-calc-py-L13-L33):

``` python
def expected_dmg_from_round(
    th_d20_outcome: int,
    th_d6s_outcome: int,
    dc_d20_outcome: int,
    dc_d6s_outcome: int,
    th_mod: int,
    dc_mod: int,
    dmg: int,
    arm: int,
    rof: int,
):
    modded_th = th_d20_outcome + th_mod + th_d6s_outcome
    modded_dc = dc_d20_outcome + dc_mod + dc_d6s_outcome
    hits = max(0, modded_th - modded_dc + 1)

    if th_d20_outcome == 20:
        hits = max(hits, 1)
    elif th_d20_outcome == 1:
        hits = 0

    return min(rof, hits) * max(0, dmg - arm)
```

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.


  [1]: https://i.sstatic.net/vWZWK.png
  [2]: https://mybinder.org/v2/gist/posita/c479522963f34c149e22871667b5cc03/HEAD?labpath=_dpr.ipynb