if the numbers in sequence

[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

 

[muddan madhu]

Try this

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

Not just enter, Use Ctrl + Shift + Enter

 

[T. Valko]

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.

 

[baha]

Try this

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

 

[T. Valko]

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)

 

[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

 

[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

 

[T. Valko]

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.

 

[T. Valko]

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.

 

[baha]

Wow that works well)))
Actually I wrote a three different code to make the same thing,but
this is so practical. thaks
Baha

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[herochenna]

Visit www.ExcelGoodies.Com