Count areas within a range 1

[N1GHTEYES]

Hi,
I am trying to find a quick way of counting the number of contiguously connected sub-ranges, within a larger range, that meet a specified criterion. For example, in the range A1:C10, there may be say, 7 cells with values above some specified threshold, let's say 1 for the sake of argument.
So, assuming that they held data, then I could use: ={Count(if(A1:C10>1,A1:C10)} to count the number of cells matching my criterion, however I do not want the number of cells per se, but the number of groups of cells.

Say, for example, the cells which are thresholded are:
A2:B3, A5, B8:B9

Then the answer I am looking for is 3, because there are 3 contiguous groups.

Supposedly the Areas function provides what I need, but it does not seem to work.

If I use: {=AREAS(IF(A1:C10>1,A1:C10))} (entered as either a standard formula or an array) I get a VALUE! error.

Can anybody suggest what I might be doing wrong please?

 

[SkipVought]

What you have defined is 3 ranges, perhaps each range containing contiguous data?

Contiguous ranges can be identified using Current Region via VBA.

Not knowing your goal, we are hampered.

Various disparate ranges can be defined with or without data as Named Ranges also.

Where are going with all of this? A concrete example like a sample workbook would be great.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[N1GHTEYES]

Hi Skip,

The details are not germane, but essentially what I am doing is scoring a 2D dataset based on some criteria (actually it is how well it correlates to another dataset but that really does not matter).

I can set a user-defined threshold of score which is considered significant.
So for a given threshold some cells within the data will meet the criterion and some won't.

What I need to know is not how many cells meet the scoring threshold criterion, but how many connected clumps of cells do so.

There are ways in which I can code that mandraulicly, and I could use that to create a UDF to do the job. But it looked to me like Areas might be roped in to do it for me.

So I wanted to define a single range containing the relevant data, then subdivide it using an If statement and a threshold in an array formula, into smaller areas, and then use Areas to count those areas.

Is that clear? An equivalent case might be if the cells of the chosen range represented the pixel values of an aerial image of some ice floes in an arctic bay. You have been tasked with counting the floes - not the pixels comprising the floes, but the floes themselves. So you threshold all the bright pixels and count the resultant clumps.

I am guessing that Excel cannot be cajoled into doing what I want, but if anybody has any suggestions I am all ears.

 

[SkipVought]

BTW, the answer to your first question...

=AREAS((A2:B3, A5, B8:B9))

returns 3. DUH!

I never found AREAS() to be very helpful.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

The details are not germane

I guess we're fighting germane warfare.

What happens in Vagueness, stays in Vagueness!

Sorry.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[N1GHTEYES]

Hi Skip,

Yes, =AREAS((A2:B3, A5, B8:B9))returns 3. But what I want is to have a workbook with some data on it, and to be able to change the value in a cell which represents an acceptable threshold for the data. As that threshold changes, the set of cells which are thresholded change. What I want to count is see how the number of clumps of thresholded cells changes as I change the value in the threshold-defining cell.

So I cannot manually pre-assign the range subsets into the Areas function (as above) - because the selected cells, and therefore the clumps alter responsively.

 

[SkipVought]

You can "float" a range formula in Excel via the OFFSET() function. Here's one way:

1) Anchor the function and make every subsequent result relative to A1. That's the first of 5 arguments.

2) Vary the Row & Column offsets, arguments 2 & 3 to get to the Upper-Left cell of the intended floating range.

3) Vary the Row & Column count of the floating range using arguments 4 & 5.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

So...
With the suggestion above, OFFSET(), it sure would be helpfull, to YOU, if you would provide a testset as requested before, in order that we (the royal we) could actually see and understand and might then offer cogent and relevant suggestions rather than trying to divine your intent and actual need and continue throwing darts whilst vision deprived.

And along with a testset, a statement of requirements for searching, setting an area and returning a value(s).

But who am I, but the son of a common man.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mintjulep]

Mandraulic, that's a new word for me!

 

[mintjulep]

As a starting concept:

Public Function MakeArea(Target As Range, Test As Integer) As Range

Dim myArea As Range
Set myArea = Nothing


For Each myCell In Target
    If myCell.Value = Test Then
        If myArea Is Nothing Then
            Set myArea = myCell
        End If
        Set myArea = Union(myCell, myArea)
    End If
Next

Set MakeArea = myArea
    
End Function

Use:
=AREAS(MakeArea(A14,1))

Limitation: Areas seem to be contiguous only if cells touch top-bottom or left-right. Cells that touch at corners are not contiguous.

 

[N1GHTEYES]

Thanks Mint.

I begin to suspect that there probably is no way to do this directly via worksheet functions alone. But your code is neat and does the job. And it is easier than coding the whole object (clump) selection & counting process directly.

Thank you very much.

Ah, I just spotted another limitation, not only does it only count full edge connections (i.e. corner connected cells are not considered to be connected, but it goes further than that. Only rectangular clumps are counted. So, an L-shaped range counts as 2 objects for example. Or if an entire rectangular block is thresholded except for a single cell somewhere in the midst of the block, then the block counts as 4 objects.

Oh well, so much for that idea...

 

[mintjulep]

"Only rectangular clumps are counted."

Too bad.

So, it seems that you'll need to write your own function that loops through each cell in the range and tests each of the cell's eight neighbors for AND(your condition, inside the range)

 

[N1GHTEYES]

Actually, there are more efficient ways of doing it, and I do have some old code knocking around somewhere that I wrote years ago, but it was for a different job, and made some assumptions which are not true in this instance, so it would need some mods. I was just trying to be efficient (lazy) and hoping that Excel would do it for me. Or I could just get one of "da yoof" to do it in Matlab, which has an inbuilt function to do it. I hate Matlab.

Your contribution was appreciated nonetheless. Thanks.

I used to use Tek-Tips very regularly, until about a decade ago when our IT high-muckamucks decided it was an "unacceptable risk to the company IT infrastructure". However, in the post-covid days of home working, I have my personal home PC sitting next to my work laptop, so swapping from one to the other is a doddle. I may therefore be back more often - at least on the days I am working from home. Not that I have much time for coding nowadays.

I am slightly surprised to still be seeing that quite so many names from the past are still here.

Tony

 

[DrSimon]

I guess we're fighting germane warfare.

What happens in Vagueness, stays in Vagueness!

Those are really very good Skip and I gave you a star for both of them. Hope they were original and you deserved them

 

[SkipVought]

DrSimon, I really like words. They are rather essential, especially The Word.

Even as a boy of 3, I am told, I was associating words in, for instance, "The Night Before Christmas" like "...not even a mouse" became "...not even a pussycat or a mouse"

So germane just came to me as relevant in the somewhat combative conversation above.

However, for the vagueness statement, I can take no credit. It is on a T-shirt of mine.