Ok, so I've known what I want to do for the remaining characters in One on One vs a Bloodthirster for a while now, but quite frankly the next one up to the plate has me severely over my head from a mathematical standpoint. I think a huge part of the problem is that the scope of it simply overwhelms me, and it's been way too long since I've done advanced enough math to be able to figure out how to properly express the problem cleanly. As I need to run the calculations to see how far off I am from my target, the thought of trying to do it piecemeal and the long, slow, arduous by hand methods I have readily available to me, then having to redo it after tweaking all the variables scares me. Still, it's been far too long, and maintaining the surprise is only worth something if it actually gets done. So, without further ado, my problem.
The next one up to the plate is the Apothecary, who is going to be a Black Templar Sword Brother in Terminator Armour, wielding dual Assault Cannons, just blasting apart the cover the Bloodthirster hides behind like a nutcracker smashing an outer shell to get at the relatively fleshy nut within. He's going to end up testing against 135 (base 50, maxed, +5 from armour history, for a BS of 75, then +60. Size alone gives +30, full auto gives +20, and hatred gives +10. Need to balance out the penalties for range and two weapon wielding, but there are *plenty* of ways to do that, including sig. wargear and motion predictors, so it ends up at 135 one way or another). The Bloodthirster, not being a fool, dodges with a dodge value of 40 (due to a targeter which I'll definitely be picking up). The attacks are dealing so far 2d10(tearing) + 12 + 2 (master crafted) + 4 (detestation), pen 6, felling (master of arms), for a final value of 2d10 + 18 - (cover + 19), with the cover having a starting value of 12 and being reduced with each hit.
So, there's a 5% chance he'll miss entirely, and though normally I'd just assume a reroll with a fate point, there's so much stuff going on here that I'll just leave it at the 5% chance of 0 hits it by default would represent. 10% chance each of 4, 5, 6, 7, 8, and 9 hits, and a 35% chance of 10 hits. So far so good, except you need to apply those chances to both attacks, so (as an example), the 10% chance of 4 hits is actually .5% chance of 4 hits, 1% chance each of 8, 9, 10, 11, 12, and 13 hits, and 3.5% chance of 14 hits. Next you would need to factor in the chance to dodge. Thankfully (about my only break in all of this) since the minimum number of hits on one attack (assuming it's greater than 0) is equal to the maximum number of attacks the Bloodthirster can dodge, so we don't need to worry about whether he dodges the first or second attack, or dealing with reducing the number of hits dodged because he dodged 'excessively' well. So, following that example, it's a .3% chance of 4 hits, and a .05% chance each of 3, 2, 1, and 0 hits. Similarly, it's a .6% chance of 8 hits, and .1% chance each of 7, 6, 5, and 4 hits.
This excessive amount of calculation is compounded *severely* by the interaction of those hits with cover. The first hit does 2d10 tearing - 13 minimum 0, the second does 2d10 tearing - 12 minimum 0, and so on and so forth, leading to the 12th hit and onwards, which are 2d10 tearing - 1 minimum 0. I worked out all 1,000 values provided by 2d10 tearing, but I lack the mathematical or excel knowledge necessary to have it neatly subtract a number from a field or table, and then have it sum the positive values that remain (while ignoring the negative ones) and divide that by 1000 to get the average damage per hit for those 12 different values, and doing it by hand would take *forever*.
More pressingly, since the average damage per hit varies based on the number of hits involved, I can't just find the average number of hits, subtract the average number of dodges, multiply and call it a day. I need to actually get the full breakdown of the percentage chances of all the different possible number of hits, and then multiply that percentage by the total average damage which results from that number of hits. Again, doable by hand, but incredibly time consuming.
My hope is that someone has the math skills to help me figure out those values, or at minimum to help me get it properly formed into a mathematical expression, so that I can run it through myself, and if the variables change (I find I need a damage boost and seek out another +2 from somewhere, for example) I can re-run it with the modified variables as needed. Alternately, and possibly even more helpfully, would be if someone can help me figure out the excel side of it, how to convert the first part into a table in excel, so I can let it figure out and sum the percentage chances of the various possible number of hits for me, and / or how to perform the desired mathematical operations on the existing table for the second part.
Any and all help would be appreciated, and I do mean *any* help at all, though if you lack skills in mathematics or the use of excel, just know that I'm working on the apothecary, and though I'm reasonably certain I've gotten it done, crunching the numbers is taking me an absurd amount of time, so my apologies for that.
