EXCEL-Numbers in Sequence
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
|