I have been reading about ICM stuff and using EXCEl to build a ICM model. ICM requires to workout your probability of finishing in nth(n=1、2、3、4....) place, it is easy to workout your probability of finishing in 1st and 2nd, but it's quite complicate when 3rd 4rd 5th come in. Dose anyone has any idea how to use EXCEL to workout the problem easier?
If you have a VBA code，I hope you can share with me. Thanks a lot.

I'm not sure if this will help you much, but a buddy of mine (he goes my the name MathrimC on 2+2) made an ICM calculator for 5-max SNGs that he shared on GoogleDrive. You can find it here: https://docs.google.com/spreadsheets...MRM/edit#gid=0
Read the notes tab and maybe contact him for help.

I'm wondering if you got anywhere with construction an ICM calculator in Excel as I am trying to do the same.

The macro below may help:

p1, p2 etc are prizes for first, second etc.
s1 is the actual stack the function should return the value of
s2,s3 are the other stacks

I made this years ago and haven't used it for a long time now (I don't believe in ICM anymore) but I think there are some constraints in how it is used - e.g. if you are four handed they must be s1,s2,s3,s4 with zero for the other stacks. You can't have zero for s4 then have chips held by s5.

Test it out with some known results first.


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

wow, that looks pretty impressive.

it would help if you could take it a few steps back . . I can easily work out probability of each player finishing 1st or 2nd. but how is the probability of a player finishing 3rd, for example, worked out. As a starting point? Assume 4 players to keep the maths simple

At the moment, I'm not interested in applying probabilities to prize money as that's the simple part.

thank you

If you understand how to calculate 2nd place you also understand how to calculate the later positions - it's the same principle.

Players A-D, positions 1-4
The chance of player A coming 3rd is (expressed as A3):

A3 = B1C2 * A / (A+D) in other words, the chance that first two positions are B followed by C, multiplied by the proportion of A's stack relative to the total stacks of players not yet assigned positions
+ B1D2 * A / (A+C)
+ C1B2 * A / (A+D)
+ C1D2 * A / (A+B)
+ D1B2 * A / (A+C)
+ D1C2 * A / (A+B)

It gets a lot more complicated as you add more players and look for later positions, that's why you need to use a recursive function like the one I posted. You create the macro then just call it from the Excel cell you want a particular value to display in with =EvaluateICM( .... )

if you really want to know the chance of someone coming e.g. third specifically for some reason then call the function with prize of (1,1,1,0 ... for the first few places and then deduct the result you would get with prizes of (1,1,0,0, ... the difference between those two results will be the chance of someone coming third.

I wrote an Excel VBA ICM program. Below is the code for first 3 places and similar code is used for other places. A max of 5 places was assumed for the program also does Double or Nothing games. It’s not elegant but it works. Of course there is a lot more such as the prize structure, input, output, data checking, etc.

n = number of players (max of 10)
Stack = total of all stacks
S(i) = stack of i-th player
P3(i) = probability player i finishes in 3rd place
--------------
'1st Place
For i = 1 To n
P1(i) = S(i) / stacks
P2(i) = 0
P3(i) = 0
P4(i) = 0
P5(i) = 0
PWIN(i) = 0
Next

'2nd Place
For i = 1 To n
For j = 1 To n
If j <> i Then
P2(i) = P2(i) + S(j) * S(i) / (stacks * (stacks - S(j)))
End If
Next
Next

'3rd Place
For i = 1 To n
For j = 1 To n
For k = 1 To n

If k <> i And k <> j And j <> i Then
P3(i) = P3(i) + S(j) * S(k) * S(i) / (stacks * (stacks - S(j)) * (stacks - S(j) - S(k)))
End If
Next
Next
Next