Formula for playing cards

[Kalthof]

Dear community,

With your help I was able to solve problems in the past. I hope you can help me again.

I need a formula that enables me to filter on 3(or more) consecutive cards. The 4th card from column B in combination with 3 cards from column A.

In the enclosed example. An 8 and J gives 3 consecutive cards with T95(JT95 and T985).

With A43 a 2 gives 4 consecutive cards(A432). A 5 gives 3 consecutive cards(A435).

With 765, both an 8 and 4 give 4 consecutive cards(8765 and 7654).

Potential problems

1. The numbers 2-9 are logically consecutive for Excel. But I am concerned if 9TJ can be seen as such as well. Since they are letters and not numbers.

2. Same as in Rummikub. A234 is consecutive. QKA2 is NOT. The A is the final card for both ways.

In the enclosed file I showed for T95 how I would like to see it. I couldn't add 3 other cards. Since an 8 would not make 3 consecutive cards with A43 for example.

If something is unclear. Please don't hesitate and ask me to clarify.

Thank you for your help and effort.

[#2]

ok so if the first 3 are a sequence then it should always flag false? or should it flag true if that 4th ALSO makes a sequence (and I would guess that would be only if it makes it 4 in a row)?
and speaking of 4 in a row I'm guessing that should be a whole new column?

[#3]

@mtarler 

=OR(
ABS((SEARCH(LEFT(A150,1),cards)-SEARCH(MID(A150,4,1),cards))*(SEARCH(LEFT(A150,1),cards)-SEARCH(LEFT(B150,1),cards))-0.5)=1.5,
ABS((SEARCH(LEFT(A150,1),cards)-SEARCH(MID(A150,7,1),cards))*(SEARCH(LEFT(A150,1),cards)-SEARCH(LEFT(B150,1),cards))-0.5)=1.5,
ABS((SEARCH(MID(A150,4,1),cards)-SEARCH(MID(A150,7,1),cards))*(SEARCH(MID(A150,4,1),cards)-SEARCH(LEFT(B150,1),cards))-0.5)=1.5
)

You would only have to delete the first line of your formula so that the fourth card is always determined. Besides, if there are 4 in the row with the 4 card, then there are also three.

The formula works, I think the name "cards" was not assigned to the file in your post.

The calculation you used impresses me. I was not aware of the connection. Is that also possible for 4 digits?

[#4]

yes removing that 1st line will determine if the last card is part of a sequence but the question is if the 1st 3 are already a sequence then should it be flagged false even if that last one makes a sequence especially if it doesn't make a 4 card straight (e.g. 3,4,5,3)
as for that calculation trick, I doubt it will work for a 4 card sequence but will work out the possibilities and see...

[#5]

OK so fun with numbers here ....
If instead I had taken each set of 3 and done the product of the difference for each it will =+/- 2 which makes sense since 2 of the pairs must be off by 1 and the 3rd must be off by 2. (i.e. given numbers A, B, C then (A-B)*(A-C)*(B-C) will = +/-2 if they are a sequence)
Using a similar technique for 4 numbers will also work. If you have A, B, C, D then
(A-B)*(A-C)*(A-D)*(B-C)*(B-D)*(C-D) = +/- 12
this might be easy if you could use LET and LAMBDA functions but in old school that is 12 terms using each value 3x so not a 'small' equation for sure.

[#6]

ok I thought of a 'simpler' solution. You can use a function like VAR (VARA, VARPA, VAR.S, VAR.P) to see if a group of 3 numbers is sequential (i.e. 1.33333 or 1) or 4 numbers (i.e. 1.66666 or 1.25). In this way the formula can be shorter and mor concise. For example in the present version each line would instead of this:
ABS((SEARCH(LEFT(A3,1),cards)-SEARCH(MID(A3,4,1),cards))*(SEARCH(LEFT(A3,1),cards)-SEARCH(MID(A3,7,1),cards))-0.5)=1.5
be:
VARA((SEARCH(LEFT(A3,1),cards),SEARCH(MID(A3,4,1),cards),SEARCH(MID(A3,7,1),cards))=1

[#7]

I have noticed a few mistakes.

On flop 632. The A turn(4th card) is flagged FALSE. While this also gives 3 in a row(632A). It should be TRUE. The flop 732 has the same problem. 4 is correctly flagged TRUE, but A should be as well.
On A63 the turn 2 is not flagged TRUE. I think it has to do with the formula not recognizing A32 as 3 in a row. A72-3 has the same issue. It is basically the case on all flops that contain A2x, A3x or x32. Except with 4 cards in a row. A43+2 is flagged correctly.

On flop AK7. The Q is flagged FALSE. On AQ3. The K should be flagged TRUE. At this point it seems like when an A is involved. The formula doesn't correctly count 3 cards in a row. However it does correctly count 4 cards in a row. AQJ + K for example is correctly flagged.

On all flops with 3 cards in a row (except AKQ for some reason). All 4th cards that give a pair are all flagged TRUE. 7657 for example. This should be FALSE.

Nevertheless I am already very impressed with you guys.

Here you can see a visualization of what I mean:
https://gyazo.com/acdc726fd14c12dcfe71442ff263fc71

[#8]

@Kalthof  OK I'm sorry I found a couple of errors in the previous solutions.

a) my named constant "cards" was wrong because it has the number 1 in it.  It should have been A23... instead of A123...

b) although I included A at the beginning and end the one on the end was never and would never be used and hence no solution using A high would register.

In the attached I corrected for the above by creating "cardsLO" and "cardsHI" and checking BOTH.  I also output "3 in Flop" if that is the case and renamed the column to be 3 to a straight and added another column for 4 to a straight

[#9]

I see you also found the errors. I just looked at your reply and think my recent update should address those issues

[#10]

You are fast! I love that you isolated 4th to straight. That enables me to filter more precise.

There are 2 small things. Can you remove the '3 on flop' filter option and put them under the FALSE flag. For the turn strategy this is not relevant. Flop strategies already factor in the connectivity.
Is it maybe possible to combine the 3&4 to Straight columns? So it gives me the options: TRUE, FALSE and 4 connected. 4 connected = 4th to Straight. So basically 3 on flop replaced by 4th to straight.

Some blanks (these are averages in my worksheet) are flagged TRUE. Can you flag these as FALSE?
See Gyazo: https://gyazo.com/7346e5e68427787104329ce32414a25a

[#11]

@Kalthof   I combined the columns as requested and added a check for a blank. I return blank if either input cell is blank but you can change that.  Here is the formula:

= if((A2="")+(B2=""),"",
   if(("Check for 4card straight"="")+
        (VARPA(SEARCH(LEFT(A2,1),cardsLO),SEARCH(MID(A2,4,1),cardsLO),SEARCH(MID(A2,7,1),cardsLO),SEARCH(LEFT(B2,1),cardsLO))=1.25)+
        (VARPA(SEARCH(LEFT(A2,1),cardsHI),SEARCH(MID(A2,4,1),cardsHI),SEARCH(MID(A2,7,1),cardsHI),SEARCH(LEFT(B2,1),cardsHI))=1.25),
                 "4 connected",
                 IF(("Check for 3 connected in flop"="")+
                      (VARA(SEARCH(LEFT(A2,1),cardsLO),SEARCH(MID(A2,4,1),cardsLO),SEARCH(MID(A2,7,1),cardsLO))=1)+
                      (VARA(SEARCH(LEFT(A2,1),cardsHI),SEARCH(MID(A2,4,1),cardsHI),SEARCH(MID(A2,7,1),cardsHI))=1),
                                False,
                                OR(
                                       "Check each combo of 3 assuming ace low"="",
                                       VARA(SEARCH(LEFT(A2,1),cardsLO),SEARCH(MID(A2,4,1),cardsLO),SEARCH(LEFT(B2,1),cardsLO))=1,
                                       VARA(SEARCH(LEFT(A2,1),cardsLO),SEARCH(MID(A2,7,1),cardsLO),SEARCH(LEFT(B2,1),cardsLO))=1,
                                       VARA(SEARCH(MID(A2,4,1),cardsLO),SEARCH(MID(A2,7,1),cardsLO),SEARCH(LEFT(B2,1),cardsLO))=1,
                                       "Check each combo of 3 assuming ace high"="",
                                       VARA(SEARCH(LEFT(A2,1),cardsHI),SEARCH(MID(A2,4,1),cardsHI),SEARCH(LEFT(B2,1),cardsHI))=1,
                                       VARA(SEARCH(LEFT(A2,1),cardsHI),SEARCH(MID(A2,7,1),cardsHI),SEARCH(LEFT(B2,1),cardsHI))=1,
                                       VARA(SEARCH(MID(A2,4,1),cardsHI),SEARCH(MID(A2,7,1),cardsHI),SEARCH(LEFT(B2,1),cardsHI))=1
                                       )
                    )
    ))

As you can see I also added "comments" to help you see/understand what each part of the formula does.  These comments don't effect the calculations as they evaluate to false/0 and are part of an OR or sum.

[#12]

Thank you so much. You are the best!

Just to be sure. I don't have to add column E with this filter anymore right? Column D with it's filter is sufficient now?
=OR(VARPA(SEARCH(LEFT(A4,1),cardsLO),SEARCH(MID(A4,4,1),cardsLO),SEARCH(MID(A4,7,1),cardsLO),SEARCH(LEFT(B4,1),cardsLO))=1.25,VARPA(SEARCH(LEFT(A4,1),cardsHI),SEARCH(MID(A4,4,1),cardsHI),SEARCH(MID(A4,7,1),cardsHI),SEARCH(LEFT(B4,1),cardsHI))=1.25)

Since you are able to filter out 4 connected cards. Are you able to filter out ''gutshots'' as well? What I mean by that is that exactly 1 card makes a straight.

So for example 567+9, the 8 makes the straight here.
Or 568+9, a 7 makes the straight.
Or 579+6/8, a 6 or 8 make a straight
Or 569+8, 7 makes the straight

Thank you again. You have no idea how much I appreciate you!

[#13 · Accepted solution]

correct the prior Column E should have been deleted
so basically 4 cards to an inside straight possibilities. I will see if I can come up with something but it will cost you. jk. but a few likes wouldn't hurt. lol.

[#14]

No worries. You deserve all the likes I can give you haha. And indeed this is always an inside straight.

[#15]

@Kalthof "Gutshot" added

[#16]

Thank you so much. You are amazing!

I have 2 final questions. Is it possible to rename TRUE for ''3 Connected''? And FALSE for ''Non''.

And in the Name Manager. Besides cardsLO and cardsHI, do I also have to create the Table1 name? I did now but I am not sure if it is even necessary.
https://gyazo.com/37d8c728613ad7313be7b14a53f76080

For as far I can see the formula works perfectly. Many thanks!

[#17]

I read I can add quotation marks with text to the formula. But it has become so big that I am not sure where to put it in the formula.

[#18]

you can certainly sub different outputs as you see fit
in the name manager, when you click on the table range and say format as a table excel will insert that name into the name manager (i.e. you don't need to and shouldn't as I don't know how excel will "take" it and if it will auto add rows correctly)
and I added those "this does xyz"<>"" part as a way to describe what that part of the formula is doing. It does make the formula longer but it is already really long with out them so a little more doesn't hurt and could be really really helpful a year from now when you or someone else try to figure out what it is doing (IMHO)

[#19]

if i miss-read your last question and you are asking HOW to add more text notes like I did the answer is that I used the OR(....) statements to add an additional term in the for ("this does xyz"="") which will always be FALSE and therefore never 'add' or affect the outcome of the OR() statement. Another alternative is if you have (or add) an AND() in which case you simply replace the = with <> in which case that comparison will always be TRUE and once again not affect the AND() statement.

[#20]

My goal is to make rename the FALSE and TRUE names in this list. I am not the only one to use this worksheet so I need to clarify things for them. In the formula you included "4 connected" for example. I really like that you included this. And I know that I can rename this. But TRUE/FALSE is not in-between quotation marks. So I don't know how the rename that. ''3 connected'' is easier to understand than ''TRUE''.
https://gyazo.com/4a92e07ce7c787ddce6c6778bf75d5f1

Can I just add the paired column to formula in the ''Straight'' column as well?
=IFERROR(IF(FIND(LEFT(B4,1),A4),"Exist","Not Exist"),"Not Exist")

I tried to add it between these lines of your formula. But it didn't work.
= IF((A4="")+(B4=""),"",
IF(("Check for 4-card straight"="")+

My goal is to have everything under 1 column. It looks cleaner and people can just choose in the list what they want to filter for.

So that the list would become like this:

- Paired

- 4 Connected

- 3 Connected

- Gutshot

- Nothing

[#21]

@Kalthof  Updated accordingly.  I left the "paired" column in just for you to compare if you like but it isn't needed now.  Are you sure you want "FALSE" shown for the rest?  maybe "n/c" instead?  What about pairs or 3 connected on flop?  Should those have anything like "n/c - pair" and "n/c - 3 connected"?