# Finding 5 consecutive numbers in a set of 7 numbers

1. ## Finding 5 consecutive numbers in a set of 7 numbers

I am trying to make a version of poker on excel but I am unable to create a way to check if the players have a straight or not after all cards are "dealt" (they are dealt with random number generators). I found a way to check if all numbers are consecutive which goes somewhat like AND(A1=B1+1,B1=C1+1, and so on. But I cannot find a quick way to apply this to all possible ways the numbers could form straights. Unless I write out every possible combination. Is there a way to make all combinations visible or just a way to speed up the process?  Register To Reply

2. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

u can follow this link for ur solution...hope it helps  Register To Reply

3. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

Ok this was a nice question.

In VBA you can:
- sort the 7 cards (as integers, make sure they count from 1 till 13)
- while sorting, set duplicates as "99" or something
so if you have the cards: Ace, Ace, 5, 6, 7, 8, 9 it should give:
1, 5, 6, 7, 8, 9, 99

- Then just apply these rules:
--> 7th number - 3th number = 4 or
--> 6th number - 2th number = 4 or
--> 5th number - 1st number = 4 or
--> ( 5th number - 1st number = 12 and 2nd number - 1st number = 9 ) (Hence the 1 can also be counted after the king (13 - 1 = 12)

Hopefully I didn't make any mistakes, but most of the times when I do something, the pro's here give way better solutions xD  Register To Reply

4. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

This is a solution to finding consecutive numbers but it does not work on doing it to check every combination, if I interpreted what you are saying correctly, I would still have to list out every combination of 5, which adds up to around 2500 combinations.  Register To Reply

5. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

Hi.

For the non-poker initiates, could you give a few examples together with your expected results?

Regards  Register To Reply

6. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

An Excel array formula to solve de problem if the data is in A1:A7 is

Formula:  `Please Login or Register  to view this content.`

If data is in A1:G1 then the formula becomes

Formula:  `Please Login or Register  to view this content.`

Don’t forget Ctrl + Shift + Enter

Best regards  Register To Reply

7. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

Sorry. The previous post did not consider duplicate values.
The following formula already considered.

Regards.  Register To Reply

8. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

This does not seem to work for me when I inputted it into my spreadsheet. Even if there is a set of consecutive numbers it returns as false. I am not sure why this is.  Register To Reply

9. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

Sorry. The formula include a named range dado and other small error. dado=A1:A7

Try this

=OR(IFERROR(AND(MATCH(SMALL(A1:A7,1)+ROW(1:5)-1,A1:A7,0)>0),FALSE),IFERROR(AND(MATCH(SMALL(A1:A7,2)+
ROW(1:5)-1,A1:A7,0)>0),FALSE),IFERROR(AND(MATCH(SMALL(A1:A7,3)+ROW(1:5)-1,A1:A7,0)>0),FALSE))

Formula:  `Please Login or Register  to view this content.`

don't forget Ctrl + Shift + End

Regards  Register To Reply

10. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

This is what I've done.
I made a code where I give strCards(1) till strCards(7) the numbers 1 to 52. (4 sets of 13)

First I devide the values by 13 so that I get the real value of the card (ex. 52 = 0, => 13 "king" ... and ex2. 14 = 1 "ace" )
Next I sorted the values and removed doubles by putting them on the end with value 99

To see if there is a straight I figured it has to be from 1 - 5, 2 - 6 or 3 - 7. Since they add up 1 each time, the results should be 4.
Except when the ace is the first card. (ace, 10, jack, queen, king) by sorting the cards.
There you get 13 - 1 = 12 AND 10 - 1 = 9. That way also this is covered

I'm sure my code can be improved A LOT... but this is just how I did it,... and it works  ``Please Login or Register  to view this content.``  Register To Reply

11. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

What I did to create the cards was make random generators with RANDBETWEEN(1,14) and then applied suits by going RANDBETWEEN(1,4). So two cells would represent a single card. But the suits do not matter in this scenario so I am simply looking for the card number to go up. So I do not think the difference of 4 applies to my set up. So how do I input this into my document? And I am not sure if your solution will work because of the fact that the numbers will be arranged differently, for example let's say in slot 1,2,3,4,5 the numbers 1,2,3,4, and 5 are there respectively, but if it is rearranged to 1,3,4,2,5 or something like that I'm not sure if your formula would work. And I cannot make it automatically sort the data, so I would need to account for all possible permutations.  Register To Reply

12. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

This definitely changed something because now it always says it is true instead of false. Again, I do not know if I applied it correctly, does it work for you when you do it in your spreadsheet?  Register To Reply

13. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

Just wondering... how to you make sure the random values are unique? And do you apply code to stop random formulas from auto-updating?

- edit
The array formula (((SO USE CTR + SHIFT + ENTER))) José gives works for straights from 2 and up... but won't see the 14, 2, 3, 4 ,5 combination.
If you can add that in the formula, then it's all done  Register To Reply

14. ## Re: Finding 5 consecutive numbers in a set of 7 numbers

Hi Evolta

The proposed issue is "Finding 5 consecutive numbers in a set of seven numbers."
I considered Ace = 1 or 14 Jack = 11; Queen = 12; King = 13.

To simplify the formula I set the Seq name as
=MATCH(\$A\$1:\$A\$7,ROW(A1:A14)+IF(ROW(A1: A14)>7,3,0),0)
Formula:  `Please Login or Register  to view this content.`

Thus the desired formula is
=OR(IFERROR(AND(MATCH(SMALL(Seq,1)+ROW(1:5)-1,Seq,0)>0),FALSE),
IFERROR(AND(MATCH(SMALL(Seq,2)+ROW(1:5)-1,Seq,0)>0),FALSE),
IFERROR(AND(MATCH(SMALL(Seq,3)+ROW(1:5)-1,Seq,0)>0),FALSE),
AND(MATCH(ROW(1:5)+IF(ROW(1:5)=1,13,0),A1:A7,0)))
Formula:  `Please Login or Register  to view this content.`

Best regards  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1