if the numbers in sequence

  • Thread starter Thread starter baha
  • Start date Start date
B

baha

Hi All,
I have a 7 cells in a row like A1,A2,A3...A7. Each cell contains
numbers from 1 to 13. Is there any way to make a formula to calculate
if there is any sequence of 5 numbers. example
if
A1=5,A2=7,A3=8,A4=9,A5=10,A6=3,A7=4 not sequence
A1=6,A2=7,A3=8,A4=9,A5=10,A6=3,A7=4
 
Try this

=IF(AND(A2>A1,(A2:A5)-(A1:A4)=1),"Sequence","Not Sequence")

Not just enter, Use Ctrl + Shift + Enter
 
Try this array formula** .

Assumes no empty cells.

=IF(MAX(FREQUENCY(IF(A2:A7-A1:A6=1,ROW(A2:A7)),IF(A2:A7-A1:A6<>1,ROW(A1:A6))))>=4,"Yes","No")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Try this
Click to expand...
Thank you very much for the answer Muddan.
I also came up with the answer of
=IF(A1+1=A2,IF(A2+1=A3,IF(A3+1=A4,IF(A4+1=A5,"str","noo"))))
but your function formula and mine both works only for one solution;
from first cell to fifth cell.
That way I need to copy down for three cells up to work A7 to get the
answer any sequence order from
7 cells. Any idea to narrow it down to one formula?
Baha
 
Note: I've assumed the sequence must be in ascending order.

1,4,5,6,7,8,11 = Yes (4,5,6,7,8 = 5 consecutive cells in ascending sequence)
 
Hi Valko,
What if they are not in ascending order but the numbers are correct. I
sort the numbers before the above formula works.Is that possible
without sorting but we do have a sequence order. Like a straight in
poker, you might have 7 cards to look up,but the numbers does not
have to be in ascending order. It might be 2,5-6,7,10,4 & 3 but you
hit a straight like 2.3.4.5.6 that is something I am trying to come
up
with.
Thanks for helping
Baha
 
Hi Valko,
What if they are not in ascending order but the numbers are correct. I
sort the numbers before the above formula works.Is that possible
without sorting but we do have a sequence order. Like a straight in
poker, you might have 7 cards to look up,but the numbers does not
have to be in ascending order. It might be 2,5-6,7,10,4 & 3 but you
hit a straight like 2.3.4.5.6 that is something I am trying to come
up
with.
Thanks for helping
Baha
 
So, you're trying do some kind of poker simulation?

Well, that makes things exponentially more complicated because now you have
to account for pairs, trips and quads.

Let me see if I can come up with something.
 
Try this...

Assuming your unsorted numbers are in the range A1:A7.

Extract the unique numbers in ascending order to a new range. Let's assume
that range is C1:C7.

Enter this formula in C1:

=MIN(A1:A7)

Enter this array formula** in C2 and copy down to C7:

=IF(C1=0,0,MIN(IF(A$1:A$7>C1,A$1:A$7)))

Use this array formula** to count the range C1:C7 for the consecutive
sequence:

=IF(MAX(FREQUENCY(IF(C2:C7-C1:C6=1,ROW(C2:C7)),IF(C2:C7-C1:C6<>1,ROW(C1:C6))))>=4,"Yes","No")

Note how the range references are offset.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Wow that works well:))))
Actually I wrote a three different code to make the same thing,but
this is so practical. thaks
Baha
 
Back
Top