Hello all. I’d like to apologize in advance for the lenghty post. I’m a math illiterate, so I’ll probably spend a lot of time explaining something which is (hopefully) simple to solve. Also, I’ll present my problem in layman/excel terms, so please bear with me 
I play a miniature wargame where dice rolls represent how well the miniatures fight and shoot at their opponents. I’m putting together a spreadsheet to look at the statistical averages of these dice rolls (specifically shooting, but that won’t be too important) and I’ve hit a brick wall.
First the basic rules: When you archers shoot at a target, there’s one roll to hit and a second roll to wound, and there are several modifiers to influence both rolls. For example: Your average archer will hit on 4+ (4,5 or 6 on the dice) and wound on 4+. So ten archers will, on average, cause 2,5 wounds. This I can do
There are different kinds of archers. Some have better aim and some have bigger bows (there are also different targets and some are harder to wound than others, not particularly important).
Part of the game is choosing which units to field and for choosing this, all units have a points cost, somewhat representing their effectiveness and I’m trying to compare their shooting effectiveness to their points cost.
For example, we have the Regular Archers who hits on 4+ and wounds on 4+ and then we have Master Archers who hit on 3+ and wounds on 3+. If the Regular Archers cost 20 points their effective output would be 20*2*2 = 80 points to cause a wound. If the Master Archers cost 40 points it would be 40*1.5*1.5 = 90 points to cause a wound. So in this case the Regular Archers are actually the better choice. There are a ton of other variables, which is why I’m making the spreadsheet (sometimes their performance is different against different targets, there are other dice rolls, not relevant to my question, and all this makes it so complicated that I actually need a spreadsheet to get an overview).
My question regards a different mechanic caused by a special rule in the game. Some arrows have poison on them and if you fire a poisoned arrow and roll a 6 on the to-hit roll, it means you automatically wounds (no need to roll that dice again). This mechanic is too difficult for me to find a solution to. I can do it somewhat crude if the calculation is the other way around (if I start with the number of archers and try to figure out how many wounds they do), but I can’t make it work when I’m trying to find their “cost/effective damage output”.
A few things to note: On both rolls, a 1 is always a failure and on both rolls a 6 is always a success (well, actually there is a different rule regarding 6’s on the to-hit roll, but you can see that in the bonus question).
If you want I can post the formulas I already made for the regular shots, but they only really make sense if I explain the different variables (matching the Strength (hitting power) of the weapon versus the Toughness of the target, for example).
This is a somewhat crude version of how I’ve written it:
Ppm*6/(x)*6/(y)
Ppm = points per model
X = a number of variables regarding the to-hit rolls
Y = a number of variables regarding the to-wound rolls
I really don't know how I should subtract 1/6 of the to-hits and add them directly to the to-wounds part of the equation.
Any help would be greatly appreciated. If there’s anything I can do to explain myself further, please feel free to ask. If this is the wrong forum, please do tell me. English is not my first language (and my last math lesson was ~20 years ago), so I don’t really know what the different subforums mean
Again, thank you
The bonus question: There’s another rule, let’s call it Lucky Shot. If the to-hit modifiers makes you hit on a 7 (on a 6-sided die), it’s possible as long as you roll a 6 and then 4+. 8 is 6 and 5+, 9 is 6 and 6. But if you need a 7 to-hit it’s not possible to gain the benefits of the poison. This part of the question is of really low priority as it’s fairly rare.
I play a miniature wargame where dice rolls represent how well the miniatures fight and shoot at their opponents. I’m putting together a spreadsheet to look at the statistical averages of these dice rolls (specifically shooting, but that won’t be too important) and I’ve hit a brick wall.
First the basic rules: When you archers shoot at a target, there’s one roll to hit and a second roll to wound, and there are several modifiers to influence both rolls. For example: Your average archer will hit on 4+ (4,5 or 6 on the dice) and wound on 4+. So ten archers will, on average, cause 2,5 wounds. This I can do
There are different kinds of archers. Some have better aim and some have bigger bows (there are also different targets and some are harder to wound than others, not particularly important).
Part of the game is choosing which units to field and for choosing this, all units have a points cost, somewhat representing their effectiveness and I’m trying to compare their shooting effectiveness to their points cost.
For example, we have the Regular Archers who hits on 4+ and wounds on 4+ and then we have Master Archers who hit on 3+ and wounds on 3+. If the Regular Archers cost 20 points their effective output would be 20*2*2 = 80 points to cause a wound. If the Master Archers cost 40 points it would be 40*1.5*1.5 = 90 points to cause a wound. So in this case the Regular Archers are actually the better choice. There are a ton of other variables, which is why I’m making the spreadsheet (sometimes their performance is different against different targets, there are other dice rolls, not relevant to my question, and all this makes it so complicated that I actually need a spreadsheet to get an overview).
My question regards a different mechanic caused by a special rule in the game. Some arrows have poison on them and if you fire a poisoned arrow and roll a 6 on the to-hit roll, it means you automatically wounds (no need to roll that dice again). This mechanic is too difficult for me to find a solution to. I can do it somewhat crude if the calculation is the other way around (if I start with the number of archers and try to figure out how many wounds they do), but I can’t make it work when I’m trying to find their “cost/effective damage output”.
A few things to note: On both rolls, a 1 is always a failure and on both rolls a 6 is always a success (well, actually there is a different rule regarding 6’s on the to-hit roll, but you can see that in the bonus question).
If you want I can post the formulas I already made for the regular shots, but they only really make sense if I explain the different variables (matching the Strength (hitting power) of the weapon versus the Toughness of the target, for example).
This is a somewhat crude version of how I’ve written it:
Ppm*6/(x)*6/(y)
Ppm = points per model
X = a number of variables regarding the to-hit rolls
Y = a number of variables regarding the to-wound rolls
I really don't know how I should subtract 1/6 of the to-hits and add them directly to the to-wounds part of the equation.
Any help would be greatly appreciated. If there’s anything I can do to explain myself further, please feel free to ask. If this is the wrong forum, please do tell me. English is not my first language (and my last math lesson was ~20 years ago), so I don’t really know what the different subforums mean
Again, thank you
The bonus question: There’s another rule, let’s call it Lucky Shot. If the to-hit modifiers makes you hit on a 7 (on a 6-sided die), it’s possible as long as you roll a 6 and then 4+. 8 is 6 and 5+, 9 is 6 and 6. But if you need a 7 to-hit it’s not possible to gain the benefits of the poison. This part of the question is of really low priority as it’s fairly rare.
