# Formula Challenge 019 – Length of the largest contiguous range

#### jeffreyweir

##### Active Member
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]
Code:
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.

#### jeffreyweir

##### Active Member
Sweeet! Only 47 characters, too.