Formula Challenge 019 – Length of the largest contiguous range

[jeffreyweir]

Formula Challenge 019 – Length of the largest contiguous range


Challenge description

Write a formula that returns a count of the largest contiguous block within a 1d array, d. By 'contiguous' I mean an array of data bounded either by a blank cell, or the edge of the range.


Challenge setup

Put the following data in A1:A31

[pre]

0
0
0
1
45
5
999

10
0
-21
5454
-1
5
4
5
0
0
0

5
4
98
4
4
0
65
4
45
74

[/pre]
Assign the name 'd' to this range.


Write a formula that returns a count of the largest contiguous block of data, including zeros. In the case of our sample data:

• The first contiguous block is 7 cells

• The 2nd contiguous block is 11 cells

• The 3rd contiguous block is 10 cells

• So the largest contiguous block of cells covers 11 cells


Rules

* No whole column references

* No intermediate formulas

* No VBA

* No biting, hitting, scratching, or eye gouging. I want a good clean fight, people.

* The formula should work no matter where the source data is shifted in the worksheet.

My formula length: 53 characters.

 

[NARAYANK991]

Hi Jeff ,


This seems to work ; can you confirm ?


=MAX(FREQUENCY(IF(d<>"",ROW(d)),(d="")*ROW(d)))


To be entered as an array formula , using CTRL SHIFT ENTER.


Copied from here : http://www.excelfox.com/forum/f2/counting-consecutive-values-in-an-array-668/


Narayan

 

[jeffreyweir]

Sweeet! Only 47 characters, too.