Forum Discussion
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...
- 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.
please take a look at my last post and see if that is right. I think it is working.
I get the #NAME? error. See new Gyazo.
https://gyazo.com/330c89121f5ff28c4ae7b374ea79db64
Can you maybe post the worksheet including the formula? Or will that not make a difference?
I tried adding the filter on my phone as well but same error.
I have now added a relatively wild part to my formula that checks for 4 in a row. My formula had not done that before. I'm not sure if Excel 2019 can do the formula. Theoretically, yes.
SUMPRODUCT(--(AGGREGATE(15,0,CHOOSE(ROW($1:$4),SEARCH(LEFT(A4,1),cards),SEARCH(MID(A4,4,1),cards),SEARCH(MID(A4,7,1),cards),SEARCH(LEFT(B4,1),cards)),ROW($1:$4))= ROW(OFFSET(A$1,MIN(CHOOSE(ROW($1:$4),SEARCH(LEFT(A4,1),cards),SEARCH(MID(A4,4,1),cards),SEARCH(MID(A4,7,1),cards),SEARCH(LEFT(B4,1),cards)))-1,1,4))))The result would look like this:
Enclosed is the document for you to try out.
- did you define "cards" as a the name? Click on Formulas then Name Manager then New and add:
"cards" and use ="A123456789TJQKA"
or just open the attachment
