Forum Discussion

Kalthof's avatar
Kalthof
Brass Contributor
Sep 07, 2022
Solved

Formula for playing cards

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...
BI & Data Analysis
Formulas and Functions
  • mtarler's avatar
    mtarler
    Sep 09, 2022
    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.
dscheikey's avatar
dscheikey
Bronze Contributor
Sep 08, 2022

Kalthof 

Hello Jeroen, the bad news is that with a #NAME? error, your Excel version does not recognise one of my formulas or functions. Before I can look further into the solution, I need to know which function is not working for you.

Please open the attached sheet and perform a new calculation in the worksheet so that the formulas are recalculated. Which formula(s) do not work?

 

 

Kalthof's avatar
Kalthof
Brass Contributor
Sep 08, 2022
I have to paste the formula in A1 from Error Test in which cell of the worksheet Playing cards?
Do I also have to paste the formulas/functions from cell A3-A16 somewhere?

I have Microsoft Office Home and Student 2019.
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Sep 08, 2022

    Kalthof 

    No, you do not have to copy anything. Please just run a new calculation of the cells (F9) in Error Test and tell me in which cells (A3:A16) the #NAME? error appears.

    • mtarler's avatar
      mtarler
      Silver Contributor
      Sep 08, 2022

      dscheikey I don't think any of the new formulas will work for them as they are using 2019.  Maybe this will work?

      =OR(
AND(ABS(SEARCH(LEFT(A3,1),cards)-SEARCH(MID(A3,4,1),cards))=1,ABS(SEARCH(MID(A3,7,1),cards)-SEARCH(MID(A3,4,1),cards))=1,LEFT(A3,1)<>MID(A3,7,1)),
AND(ABS(SEARCH(MID(A3,7,1),cards)-SEARCH(MID(A3,4,1),cards))=1,ABS(SEARCH(MID(A3,7,1),cards)-SEARCH(LEFT(B3,1),cards))=1,MID(A3,4,1)<>LEFT(B3,1))
)

      Since there are only 4 cards then either cards 1,2,3 or 2,3,4 need to have a sequence to work ... oh shoot there is another scenario ... the "inside straight draw".  I'll think how to add another condition for that ... but for now the attached will look for 3 cards in a sequence. (BTW "cards" is a defined name for the text string "A123456789TJQKA".

      Let me know if we are at least going in the right direction.

      • mtarler's avatar
        mtarler
        Silver Contributor
        Sep 08, 2022
        based your prior reply I saw that a sequence of 3 could pre-exist and should be detected
        also an out of sequence consecutive (inside straight) should also be detected right? (i.e. 3,1,2)
        and in 'inside 4 card straight' should be detected right? (i.e. 3,4,1,2)
        but not accept if a middle card is not part of the sequences (i.e. 3,4,8,5) should be FALSE
        and based on your formula for "Paired" is it true no initial 3 cards will have a pair?
    • Kalthof's avatar
      Kalthof
      Brass Contributor
      Sep 08, 2022

      I ran the calculation but no #NAME? appears.

      Here is a Gyazo to show you that. You can also see what happens if I filter on true in the other worksheet.

      https://gyazo.com/ad72f19e4622866fe34c5ccfe44d5c84