Formula To Count Consectives.

[CARBOB]

I need 2 formulas. The Digits in Col's B:F, range from 1-36, In Col G, I would like a formula that will return a Y(Yes) or N(No). Y if any of the digits are consective, N if none. In Col H, I would like a formula that will count the number of Consectives. Ex if B:F has 25,26, return a 2; 25,26,27, return a 3. Can this be done. All suggestions appreciated.
Thanks!!!

EL FANTASY 5 REPEATS.xlsm
	B	C	D	E	F	G	H
3	22	27	28	33	36	Y	2
4	06	12	18	19	20	Y	3
5	02	13	16	18	26	N	0
6	19	24	31	32	33
7	07	11	21	26	35
IMPORTED FILES

 

[Gerald Higgins]

Duplicate post
http://www.mrexcel.com/forum/showthread.php?t=324475

 

[Domenic]

Provided that the numbers are listed in ascending order, as per the sample data, try...

G3, copied down:

=IF(SUMPRODUCT(--(C3:F3-B3:E3=1))>0,"Y","N")

H3, copied down:

=CHOOSE(SUMPRODUCT(--(C3:F3-B3:E3=1))+1,0,2,3,4,5)

Hope this helps!

 

[DonkeyOte]

calculate consecutives first and then if > 0 use Y else N

simple way to do this would be to use an IF in Col H

=IF(C2=B2,1,0)+IF(D2=C2,1,0)+IF(E2=D2,1,0) and so forth for all cells... that would give you total number of consecutives

in col G =IF(H2>0,"Y","N")

EDIT: Domenic - NICE!

 

[CARBOB]

Thank you!!! Very Good!!

Provided that the numbers are listed in ascending order, as per the sample data, try...

G3, copied down:

=IF(SUMPRODUCT(--(C3:F3-B3:E3=1))>0,"Y","N")

H3, copied down:

=CHOOSE(SUMPRODUCT(--(C3:F3-B3:E3=1))+1,0,2,3,4,5)

Hope this helps!