We wanted to create a quick spreadsheet to rapidly adjust enemies' Attack and Defense from players' estimated values of Attack and Defense.
One of the key values to adjust is the "hits until dead", and to derive this we need an average of the damage.
A basic calculation of the damage is done as following. Both players and enemies have minimum
and maximum
values for attack
and defense
. Two random values are generated between these and the difference is the damage received. Note that the minimum damage is zero (or miss).
attack = random(attack_min, attack_max);
defense = random(defense_min, defense_max);
damage = max(attack - defense, 0);
When attack_min > defense_max
, a simple formula like this can be used to obtain the average damage:
attack_mean = (attack_min + attack_max) / 2;
defense_mean = (defense_min + defense_max) / 2;
damage_mean = attack_mean - defense_mean;
But in the other case, there is a chance of missing, and this formula is not accurate.
Let this be an example where the initial formula works:
attack_min = 10; attack_max = 20;
defense_min = 0; defense_max = 5;
attack_mean = (10 + 20) / 2 = 15;
defense_mean = (0 + 5) / 2 = 2.5;
damage_mean = 15 - 2.5 = 12.5;
And, this is an example where it does not:
attack_min = 10; attack_max = 20;
defense_min = 5; defense_max = 35;
attack_mean = (10 + 20) / 2 = 15;
defense_mean = (5 + 35) / 2 = 20;
damage_mean = 15 - 20 = -5;
There is low chance, but there is damage between 5 and 15! Empirical values suggest an average of around 2.
So, the request is the following: we need a formula that gives us the average damage regardless of whether defense is higher than attack or not. (We need a formula, not a procedure, so RNG is off the table).
Can anyone enlighten us on this? Thank you!!
Thanks to @DMGregory's answer, a reader may find the Excel VBA custom formula below, in case it can suit they.
Function TriangleSum(n)
TriangleSum = WorksheetFunction.Max(n ^ 3 + 3 * n ^ 2 + 2 * n, 0) / 6
End Function
Function GetMeanDamage(attackMin, attackMax, defenseMin, defenseMax)
GetMeanDamage = ( _
TriangleSum(attackMax - defenseMin) _
- TriangleSum(attackMax - defenseMax - 1) _
- TriangleSum(attackMin - defenseMin - 1) _
+ TriangleSum(attackMin - defenseMax - 2) _
) / ( _
(attackMax - attackMin + 1) * (defenseMax - defenseMin + 1) _
)
End Function