Search code examples
google-sheetspoker

Classifying flops by the Number of possible straights using spreadsheet

I am trying to write a function in Google Sheets that classifies the "connectivity" poker flops.

Inputs: We're given a list of "flops" (3 random cards). These are given as numeric values where 2=2, 3=3 ... 11 = Jack, 12 = Queen, 13 = King, 14 = ace

I want to be able to classify these flops using three different metrics:

1) Connectivity - The number of immediately possible straights: example 1: FLOP [5 6 7] has connectivity three, as 43/84/98 complete the straight. example 2: FLOP [K Q J] has connectivity two, as {AT, T9) complete the straight

2) Linkage - The number of possible straight draws: example 1: FLOP [K 9 2] has Linkage 3, as {QJ, QT, JT} are the possible straight draws. example 2: FLOP [J T 4] has Linkage 9, as {AK, AQ, KQ, K9, Q9, Q8, 98, 97, 76} are straight draws.

3) Span - The number of different ranks that can complete one of the possible straight draws. example 1: [K 9 2] has Span 3, as {Q, J, T} are the ranks that can complete possible straights. example 2: [J T 4] has Span 6, as ranks {A, K, Q, 9, 8, 7} are the ranks that can complete possible straights draws.

I can't think of a clean way to do this with Google Sheets. At first I tried some unwieldy IFS statements, but there are too many edge cases and this got out of hand too fast.

I could do this with a normal language easily enough, but I can't figure out how to do this cleanly with sheets.

Solution

  • 1) Connectivity

    I think you are right, it doesn't fit very easily into Google Sheets. My idea though is to use a matrix like this to set up a sliding window to look at groups of five out of the thirteen possible cards (2-6, 3-7, 4-8 etc.):

    enter image description here

    Then if there are only two gaps waiting to be filled to make up the run of 5 (i.e. all three cards from the draw match somewhere in this run of 5), that must constitute an item of connectivity.

    The formula ends up looking like this:

    =ArrayFormula(countif(mmult(if((column(A1:M9)>=row(A1:M9))*(column(A1:M9)<row(A1:M9)+5),1,0),
if(isnumber(match(sequence(13),match(A1:A3,B:B,0),0)),1,0)),3))

    Where column A contains the three cards drawn and column B contains a list of their possible numbers or letters 2,3,4,5,6,7,8,9,T,J,Q,K and A.

    enter image description here

    EDIT

    I think the sliding window was the right idea, but could have been written much more concisely without using an actual matrix as:

    =ArrayFormula(countif(countifs(match(A1:A3,B:B,0),">="&sequence(9,1,1),match(A1:A3,B:B,0),"<="&sequence(9,1,5)),3))

    2) Linkage

    On the face of it you can use the same sliding window idea, but this time count how many in a range of 5 have just 2 cards out of the five (so with another two cards, you have a straight draw). The trouble is that this produces double counting. Suppose you have [34X] where X is some card that we aren't interested in. The straight draws looking in the range 2-6 are {25,26,56}. Then if you move on to range 3-7, the straight draws are {56,57,67}. So 56 appears twice. The outcome of this is that you have to actually enumerate all the different pairs, check whether they could produce a straight draw in each range, then remove duplicates which is quite a lot of code - but I think can be done. You need to enumerate them to do 3) anyway.

    I produce an enumeration like this:

    =int(sequence(169,1,0)/13)

    and

    =mod(sequence(169,1,0),13)

    so the cards are numbered from 0 to 12 for convenience

    and apply the following conditions for the cards to be drawn:

    • First card less than second card
    • First card greater than equal to start of window
    • Second card less than or equal to end of window
    • First card is not in the flop
    • Second card is not in the flop.

    The conditions for the cards from the flop are:

    • First card less than second card
    • First card greater than or equal to start of window
    • Second card less than or equal to end of window
    • First card is in the flop
    • Second card is in the flop.

    There should be exactly one pair of cards from the flop, and these are combined with the possible pairs that make up four different cards in each window.

    Putting all this together and using the standard mmult formula for row and column totals you get:

    =ArrayFormula(countunique(if(mmult((int(sequence(169,1,0)/13)<mod(sequence(169,1,0),13))*( int(sequence(169,1,0)/13)>=sequence(1,9,0))* (mod(sequence(169,1,0),13)<=sequence(1,9,4))
*isna(match(int(sequence(169,1,0)/13)+1,match(A1:A3,B1:B13,0),0))*isna(match(mod(sequence(169,1,0),13)+1,match(A1:A3,B1:B13,0),0))
*(mmult(sequence(1,169,1,0),(int(sequence(169,1,0)/13)<mod(sequence(169,1,0),13))*( int(sequence(169,1,0)/13)>=sequence(1,9,0))* (mod(sequence(169,1,0),13)<=sequence(1,9,4))
*isnumber(match(int(sequence(169,1,0)/13)+1,match(A1:A3,B1:B13,0),0))*isnumber(match(mod(sequence(169,1,0),13)+1,match(A1:A3,B1:B13,0),0)))=1),sequence(9,1,1,0)),int(sequence(169,1,0)/13)&mod(sequence(169,1,0),13)))-1)

    enter image description here

    3) Span

    This is straightforward - just put the pairs into a single column and count the unique values:

    =ArrayFormula(countunique(flatten(if(mmult((int(sequence(169,1,0)/13)<mod(sequence(169,1,0),13))*( int(sequence(169,1,0)/13)>=sequence(1,9,0))* (mod(sequence(169,1,0),13)<=sequence(1,9,4))
*isna(match(int(sequence(169,1,0)/13)+1,match(A1:A3,B1:B13,0),0))*isna(match(mod(sequence(169,1,0),13)+1,match(A1:A3,B1:B13,0),0))
*(mmult(sequence(1,169,1,0),(int(sequence(169,1,0)/13)<mod(sequence(169,1,0),13))*( int(sequence(169,1,0)/13)>=sequence(1,9,0))* (mod(sequence(169,1,0),13)<=sequence(1,9,4))
*isnumber(match(int(sequence(169,1,0)/13)+1,match(A1:A3,B1:B13,0),0))*isnumber(match(mod(sequence(169,1,0),13)+1,match(A1:A3,B1:B13,0),0)))=1),sequence(9,1,1,0)),{int(sequence(169,1,0)/13),mod(sequence(169,1,0),13)})))-1)

    enter image description here