Finding consecutive values in a range

Posted by Gil on January 22, 2002 7:22 PM

Hi,
Is there forumula/function to identify if a range of cells contains consecutive data (example; year range 1998,1999,2000,2001 would be "true", while 1998,2000,2001 would be "false".

I am hoping to find a consecutive sequence, then create a min/max concatenation (1998-2001. Any tips would be greatly appreciated.

Thanks,

Gilbert

Posted by Mark W. on January 23, 2002 7:42 AM

You could use the array formula...

{=AND(OFFSET(A1,1,,COUNT(A1:A4)-1)=A1+ROW(INDIRECT("1:"&COUNT(A1:A4)-1)))}

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.

Posted by Gil on January 23, 2002 7:01 PM

Thanks a lot! (NT)