EXCEL-Numbers in Sequence

Hi,

Someone whou knows how to count the maximum amount of numbers that are in
consecutive sequence regardless of their position out of a selected numbers
of cells.

I have 7 cells which each can contain a number from 1-15.
E.g.: 1 4 3 10 8 5 11
Above the max is 3 numbers who are in consecutive sequence i.e. 3 4 5.

If someone knows how to make a formula to count this would be great.

The Gasell

Try this...

Create this defined formula.

Goto the menu InsertNameDefine
Name: Seq
Refers to: =ROW(INDIRECT("1:15"))

Ok out

Then, assuming your data is in the range A2:A8.

Array entered** :

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(Seq,A2:A8,0)),Seq ),IF(ISNA(MATCH(Seq,A2:A8,0)),Seq)))

** 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.

--
Biff
Microsoft Excel MVP


"The Gasell" wrote in message
...
Hi,

Someone whou knows how to count the maximum amount of numbers that are in
consecutive sequence regardless of their position out of a selected
numbers
of cells.

I have 7 cells which each can contain a number from 1-15.
E.g.: 1 4 3 10 8 5 11
Above the max is 3 numbers who are in consecutive sequence i.e. 3 4 5.

If someone knows how to make a formula to count this would be great.

The Gasell

On 15 Mai, 18:23, The Gasell
wrote:
Hi,

Someone whou knows how to count the maximum amount of numbers that are in
consecutive sequence regardless of their position out of a selected numbers
of cells.

I have 7 cells which each can contain a number from 1-15.
E.g.: 1 4 3 10 8 5 11
Above the max is 3 numbers who are in consecutive sequence i.e. 3 4 5.

If someone knows how to make a formula to count this would be great.

The Gasell

Hello,

If your data is in cells A1:A7, array-enter
=MAX(FREQUENCY(IF(0=--
ISNA(MATCH(ROW(INDIRECT(MIN(A1:A7)&":"&MAX(A1:A7)) ),A1:A7,0)),ROW(INDIRECT(MIN(A1:A7)&":"&MAX(A1:A7) ))),IF(--
ISNA(MATCH(ROW(INDIRECT(MIN(A1:A7)&":"&MAX(A1:A7)) ),A1:A7,0)),ROW(INDIRECT(MIN(A1:A7)&":"&MAX(A1:A7) )))))

Approach is identical to Biff's but would also work for any (not too
big) positive numbers (only 1 to 15 required here).

Regards,
Bernd

Excel 2007 PivotTable
List and rank all sequences.
With sorted Table.
http://c0718892.cdn.cloudfiles.racks.../05_16_10.xlsx
Pdf preview:
http://www.mediafire.com/file/zmznjjz5nwy/05_16_10.pdf