Produced independently of the above, but here are Excel macro functions for ICM and bubble factors.
p1, p2 is 1st prize, 2nd prize
s1, s2 is 1st stack, 2nd stack
Ordering of prizes obviously matters, ordering of stacks doesn't except that if you have e.g. 3 stacks, they must be s1, s2, s3 (in any order) and s4-s10 be assigned zeros.
Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Public Function CalcBubbleFactor(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10 As Currency) As Currency
If s1 = 0 Or s2 = 0 Then
Result = 0
ElseIf s1 > s2 Then
Result = ((EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10) _
- EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1 - s2, s2 * 2, s3, s4, s5, s6, s7, s8, s9, s10)) _
/ (EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1 + s2, s3, s4, s5, s6, s7, s8, s9, s10, 0) _
- EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10)))
ElseIf s1 = s2 Then
Result = ((EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10) _
- EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, 0.0001, s2 + s1, s3, s4, s5, s6, s7, s8, s9, s10)) _
/ (EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1 * 2, s3, s4, s5, s6, s7, s8, s9, s10, s2 - s1) _
- EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10)))
Else
Result = ((EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10) _
- EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, 0.0001, s2 + s1, s3, s4, s5, s6, s7, s8, s9, s10)) _
/ (EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1 * 2, s2 - s1, s3, s4, s5, s6, s7, s8, s9, s10) _
- EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10)))
End If
CalcBubbleFactor = Result
End Function
Public Function EvaluateICM(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10 As Currency) As Currency
If p1 = 0 Or s1 = 0 Then
Result = 0
ElseIf p2 = 0 Then
Result = p1 * s1 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)
Else
Result = (p1 * s1 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10))
If s2 > 0 Then
Result = Result + (EvaluateICM(p2, p3, p4, p5, p6, p7, p8, p9, p10, 0, s1, s3, s4, s5, s6, s7, s8, s9, s10, 0) * (s2 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)))
If s3 > 0 Then
Result = Result + (EvaluateICM(p2, p3, p4, p5, p6, p7, p8, p9, p10, 0, s1, s2, s4, s5, s6, s7, s8, s9, s10, 0) * (s3 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)))
If s4 > 0 Then
Result = Result + (EvaluateICM(p2, p3, p4, p5, p6, p7, p8, p9, p10, 0, s1, s2, s3, s5, s6, s7, s8, s9, s10, 0) * (s4 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)))
If s5 > 0 Then
Result = Result + (EvaluateICM(p2, p3, p4, p5, p6, p7, p8, p9, p10, 0, s1, s2, s3, s4, s6, s7, s8, s9, s10, 0) * (s5 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)))
If s6 > 0 Then
Result = Result + (EvaluateICM(p2, p3, p4, p5, p6, p7, p8, p9, p10, 0, s1, s2, s3, s4, s5, s7, s8, s9, s10, 0) * (s6 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)))
If s7 > 0 Then
Result = Result + (EvaluateICM(p2, p3, p4, p5, p6, p7, p8, p9, p10, 0, s1, s2, s3, s4, s5, s6, s8, s9, s10, 0) * (s7 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)))
If s8 > 0 Then
Result = Result + (EvaluateICM(p2, p3, p4, p5, p6, p7, p8, p9, p10, 0, s1, s2, s3, s4, s5, s6, s7, s9, s10, 0) * (s8 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)))
If s9 > 0 Then
Result = Result + (EvaluateICM(p2, p3, p4, p5, p6, p7, p8, p9, p10, 0, s1, s2, s3, s4, s5, s6, s7, s8, s10, 0) * (s9 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)))
If s10 > 0 Then
Result = Result + (EvaluateICM(p2, p3, p4, p5, p6, p7, p8, p9, p10, 0, s1, s2, s3, s4, s5, s6, s7, s8, s9, 0) * (s10 / (s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10)))
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
EvaluateICM = Result
End Function