• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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