Hits to Kill Script

Looks like the formatting of the script wasn't preserved when I posted. Anyway, I'll go ahead and break down the algorithm in my script. Oh, I'll say right now that it would be more accurate if I had used "attacks" instead of "hits" in my code.

My commentary is enclosed in the spoiler below because it is quite long.

 Spoiler

I'm just going to explicate the first half of the excerpt. The second half just basically uses the same stuff except with different variable names and has a couple lines to account for SE.

Code:

' Skips TT calculation if TT value is blank or empty or if Excel would have reached an error calculating factorials
' (Excel errors when trying to calculate 85 factorial or higher.)
Dim CritDam3 As Double
Dim CritChance3 As Double
Dim MinHits3 As Double
Dim MaxHits3 As Double

CritDam3 = CritDam
CritChance3 = CritChance

singlehit = 3 * (Int(TTMax * (TT + CritDam3)) + Int(ShadowDam * Int(TTMax * (TT + CritDam3))))
If singlehit < 6 Then
    If Shadow = 1 Then
    singlehit = 6
    End If
End If
If singlehit < 3 Then
    singlehit = 3
End If
MinHits3 = -1 * Int((-1) * (MonHP / singlehit))
If Shadow = 1 Then
MaxHits3 = ActiveSheet.Cells(67, 10)
Else
MaxHits3 = ActiveSheet.Cells(67, 5)
End If

This section is to calculate the values for the minimum and maximum number of attacks required to KO. The variable singlehit would be more appropriately named if it read "singleattack." Specifically, I store the maximum possible damage from a single attack in the variable. It is determined by the line: "singlehit = 3 * (Int(TTMax * (TT + CritDam3)) + Int(ShadowDam * Int(TTMax * (TT + CritDam3))))" -- and I account for exceptions in the two if-statements below it.

The Int() function in VBA takes the Floor of the value enclosed in the parentheses. TTMax is a value that can be taken off banditcom's spreadsheet. Essentially, this is the maximum value on the range after having taken WDEF (or MDEF) into account. TT+CritDam3 is just the % multiplier (e.g., 2.5 for maxed triple throw, 2.4 for Level 20 triple throw, etc.).

Anyway, we want to know the maximum number of attacks required to kill so that the script can check when to stop. The minimum number of attacks required to kill is also useful because it can be used to speed up the calculation. In particular, we know that any number of attacks less than the minimum required to kill has a 0% probability to kill, or alternatively, a 100% probability to not kill.

Code:

Dim TTArray As Double
Dim TTSEArray As Double
If IsEmpty(ActiveSheet.Range("H22").Value) = True Or TT = 0 Then
TTArray = 0
TTSEArray = 0
End If

If MaxHits3 > 85 Then
    TTArray = 0
    TTSEArray = 0
    Answer = MsgBox("Cannot compute TT Hit Distribution. Max Hits of TT must be 85 or less. Click OK to continue.", vbOKCancel, "TT Calculation Error")
    If Answer = vbCancel Then
        Exit Sub ' the macro ends if the user selects the CANCEL-button
    End If
Else

Checks whether TT skill level is greater than 0. If it is equal to 0, it sets some variables to 0 so that code later on in the script can be skipped.

The test "If MaxHits3 > 85" is very important. Later, we use the binomial coefficient to determine the probabilities so the factorial of the number stored in this variable is needed. Apparently, no errors occur when the value here is 84 or lower (I determined this through trial and error). I think Excel can actually handle up to 170!, but there's a multiplication in the denominator for the binomial coefficient.

Anyway, if the test fails, it prompts the user to say that the calculation will be skipped. The user can then choose "Cancel" to exit the script and fix the problem, or the user can choose "OK" and let the script calculate anything it can (e.g., the script can still try to calculate for Lucky Seven or Avenger).

Code:

' Declaration of the Array that holds the TT NHit Percentages
TTArray = 1
Dim NHitArray3() As Double
Dim A3 As Double
Dim B3 As Double

sum = 0
Pc = 0

ReDim NHitArray3(1 To MaxHits3)

For z = 1 To Int(MinHits3 - 1)
    NHitArray3(z) = 1
Next z

Okay, done with preliminary checks to make sure the calculation can actually be done! Now, for the actual meat of the calculation.

For now, ignore the line "sum = 0." In my earlier post, I explained that the variable "Pc" is the probability that a monster is still alive after so-and-so many attacks. Here, I am just setting their initial values before we perform any calculations.

"ReDim NHitArray3(1 To MaxHits3)" sets the length of the array in which I am going to save all the Pc values to. Then the following For-loop sets the entries in the array corresponding to a number of attacks less than the required to kill to have a value of 1. Again, I use the value '1' because there is 100% probability to not kill for these numbers of attacks.

Code:

For h = (MinHits3) To (MaxHits3)
    For i = 0 To (3 * h)
    
        ' Next two if-statements force Pc to range from 0 to (numer/denom)
        A3 = i * (Int(TTMin * (TT + CritDam3)) + Int(ShadowDam * Int(TTMin * (TT + CritDam3)))) + (3 * h - i) * (Int(TTMin * TT) + Int(ShadowDam * Int(TTMin * TT)))
        If A3 < 6 * h Then
            If Shadow = 1 Then
            A3 = 6 * h
            End If
        End If
        If A3 < 3 * h Then
            A3 = 3 * h
        End If
        If A3 < MonHP Then
            A3 = A3
        Else
            A3 = MonHP
        End If
        
        B3 = i * (Int(TTMax * (TT + CritDam3)) + Int(ShadowDam * Int(TTMax * (TT + CritDam3)))) + (3 * h - i) * (Int(TTMax * TT) + Int(ShadowDam * Int(TTMax * TT)))
        If B3 < 6 * h Then
            If Shadow = 1 Then
            B3 = 6 * h
            End If
        End If
        If B3 < 3 * h Then
            B3 = 3 * h
        End If
        If B3 > MonHP Then
            B3 = B3
        Else
            B3 = MonHP
        End If

        numer = fact(3 * h)
        denom = (fact(i)) * (fact((3 * h) - i))
        If (B3 - A3) = 0 Then
        Pc = Pc
        Else
        Pc = Pc + (numer / denom) * (CritChance3 ^ i) * ((1 - CritChance3) ^ (3 * h - i)) * ((MonHP - A3) / (B3 - A3))
        End If
    Next i
    NHitArray3(h) = Pc
    Pc = 0
Next h

The first for-loop basically tells the script for which numbers of attacks that we want to calculate Pc (probability to not kill). So the variable 'h' in each iteration of the loop is the number of attacks. For TT, there is either 0-3 criticals per attack. Thus, we nest a for-loop, and 'i' is the number of criticals for each given 'h', and the value of 'i' can vary from 0 to 3*h.

A3 is the lowest possible damage for a given 'h' and a given 'i', and B3 is the highest possible damage. Notice that A3 and B3 basically calculates the damage for TT given that we know the base range of TT (e.g., TTMin and TTMax) and its multipliers 'TT+CritDam3' when we crit and just 'TT' when we do not crit. 'i' is the number of criticals and '(3 * h - i)' is the number of non-criticals.

Following that are a couple if-statements for both A3 and B3 to account for situations where we would actually hit for just 1 damage on every hit of the attack.

The variable MonHP should be obvious (it holds the value for monster's HP). For now, ignore the if-statements that do tests comparing A3 or B3 vs. MonHP and leave the values alone or change them to equal to MonHP. I'll address these later.

'numer' is the numerical part of the binomial coefficient, and 'denom' is the denominator. Skip down to 'Pc = Pc + (numer / denom)...' This part of the line after the addition operation: '(numer / denom) * (CritChance3 ^ i) * ((1 - CritChance3) ^ (3 * h - i))' is just the binomial distribution in statistics. '(numer/denom)' is the binomial coefficient. The rest is basically a straight substitution given that:
1) 'CritChance3' is your value for p.
2) 'i' is your value for k.
3) '3*h' is your value for n.
Then the final part: '((MonHP - A3) / (B3 - A3))' is just 'probability to not kill'. Note that without the two if-statements comparing A3 or B3 vs. MonHP, then this would be inaccurate and skew your results. So that whole line after the addition operator basically says to find probability to not kill and give it the appropriate weighting for given values of 'h' and 'i'.

Since the value of 'i' can vary for a given 'h', you can see why the line reads: 'Pc = Pc + (numer / denom)...' instead of simply: 'Pc = (numer / denom)...' They have to be added together to get the correct value of 'Pc' for each given 'h'.

In the if-statement testing whether B3-A3 is zero:
For the true case, I think it might be correct if it instead was: 'Pc = Pc + (numer / denom)'. Truthfully, it's been so long since I originally wrote this script that I've forgotten why I have 'Pc = Pc' in that line.

Okay, that brings us outside of the nested for-loop. Now the final two lines of the outer for-loop just stores the value of Pc to the array and resets the value of Pc to zero so it is ready to be used in the next iteration.

Code:

' Changes each element of the array to a percentage. Then recalculates each element
' so that each element contains the percentage of KO's in exactly N-hits. Because we
' previously had KO's in at least N-hits stored in each element of the array.
For k = 1 To Int(MaxHits3)
    NHitArray3(k) = 100 * NHitArray3(k)
    NHitArray3(k) = 100 - NHitArray3(k) - sum
    sum = sum + NHitArray3(k)
Next k

The first line in this for-loop changes all the values to percentages. The other two lines change the values from 'probability to not kill' to 'probability to kill in exactly N-attacks'. (Again, it would be more appropriate if I had 'N-attacks' in the commented part of the code instead of 'N-hits'.)