Hits to Kill Script

I did something like this for the NL Damage Calculator that banditcom originally made. However, it's still unreleased because there's some issues we need to fix, and we're both extremely lazy to fix them.

Anyway, here's an excerpt of code from the script I wrote. Note that this is written in VBA (for Excel) because I wrote the script to integrate directly with banditcom's spreadsheet. Below is excerpt of code dealing with hits to kill with TT and TT w/ SE:

 Spoiler

' 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

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

' 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

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

' 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

' Declaration of the Array that holds the TT+SE NHit Percentages
If IsEmpty(SEDam.Value) = True Or IsEmpty(SEChance.Value) = True Or SEDam.Value = 0 Or SEChance.Value = 0 Or TTArray = 0 Then
TTSEArray = 0

Else
TTSEArray = 1
Dim NHitArray4() As Double
Dim MinHits4 As Double
Dim MaxHits4 As Double
Dim A4 As Double
Dim B4 As Double
Dim CritDam4 As Double
Dim CritChance4 As Double

sum = 0
Pc = 0
CritDam4 = CritDam + ActiveSheet.Cells(29, 8) + 1
CritChance4 = CritChance + ActiveSheet.Cells(30, 8)
If Shadow = 1 Then
MinHits4 = ActiveSheet.Cells(68, 10)
MaxHits4 = ActiveSheet.Cells(67, 10)
Else
MinHits4 = ActiveSheet.Cells(68, 5)
MaxHits4 = ActiveSheet.Cells(67, 5)
End If

ReDim NHitArray4(1 To MaxHits4)

For z = 1 To Int(MinHits4 - 1)
NHitArray4(z) = 1
Next z

For h = (MinHits4) To (MaxHits4)
For i = 0 To (3 * h)

' Next two if-statements force Pc to range from 0 to (numer/denom)
A4 = i * (Int(TTMin * (TT + CritDam4)) + Int(ShadowDam * Int(TTMin * (TT + CritDam4)))) + (3 * h - i) * (Int(TTMin * TT) + Int(ShadowDam * Int(TTMin * TT)))
If A4 < 6 * h Then
If Shadow = 1 Then
A4 = 6 * h
End If
End If
If A4 < 3 * h Then
A4 = 3 * h
End If
If A4 < MonHP Then
A4 = A4
Else
A4 = MonHP
End If

B4 = i * (Int(TTMax * (TT + CritDam4)) + Int(ShadowDam * Int(TTMax * (TT + CritDam4)))) + (3 * h - i) * (Int(TTMax * TT) + Int(ShadowDam * Int(TTMax * TT)))
If B4 < 6 * h Then
If Shadow = 1 Then
B4 = 6 * h
End If
End If
If B4 < 3 * h Then
B4 = 3 * h
End If
If B4 > MonHP Then
B4 = B4
Else
B4 = MonHP
End If

numer = fact(3 * h)
denom = (fact(i)) * (fact((3 * h) - i))
If (B4 - A4) = 0 Then
Pc = Pc
Else
Pc = Pc + (numer / denom) * (CritChance4 ^ i) * ((1 - CritChance4) ^ (3 * h - i)) * ((MonHP - A4) / (B4 - A4))
End If
Next i
NHitArray4(h) = Pc
Pc = 0
Next h

' 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(MaxHits4)
NHitArray4(k) = 100 * NHitArray4(k)
NHitArray4(k) = 100 - NHitArray4(k) - sum
sum = sum + NHitArray4(k)
Next k
End If
End If

Also, here is a function to calculate a factorial. I found it easier to write the function myself than try to call Excel's factorial function with VBA.

 Spoiler

Function fact(number) As Double
fact = 1
For j = 1 To Int(number)
fact = fact * j
Next j
End Function

Hopefully, the variables names I chose to use in the script should be quite evident or easily deducible. Some of the stuff might seem like they're not useful at all, but again.... the above part was an excerpt so it'd find a use outside of the above excerpt. I'll check the thread as often as I can to clear up anything else you may think is confusing.

Oh, and a note about the variable Pc in my script. At first, I calculate the probability of NOT killing for each number of hits in question. Then the line: "NHitArray3(h) = Pc" in each iteration builds an array of these values. With that, it's easy to build the array of probabilities of killing in exactly a certain number of hits -- done by that For function at the end. Kind of a roundabout way of getting the results we want, but it works. And I thought it was easier to program it in this way.