Counting areas

[N1GHTEYES]

I'm creating a workbook which pulls in data from user-specifed data files and performs some processing on it.

As far as possible, I'd like to do the bulk of the processing via dynamic named ranges. This should be faster and much neater.

I'm having a little trouble with one of the processes I want to perform. Imagine I have two ranges, x and y - both of which are 1 cell wide and N rows tall. I have also specified a threshold "Thresh". I define another range, ThreshX as:
=If(y>Thresh, x).

If I now enter =COUNT(ThreshX) in a cell I get the number of cells in ThreshX - i.e. the number of cells in which y>Thresh.

That works fine, no problem. However, I also want to count the number of peaks in x, i.e. the number of contiguous regions in ThreshX. To do so, I entered into a cell the formula:
=AREAS(ThreshX). Unfortunately, this returns the Value! error.

I have found a way round the problem, but it is slightly messy. Does anyone have any idea why the Areas method does not work, and if there is any change I could apply to the syntax I'm using which would make it work the way you'd expect?

Tony

 

[SkipVought]

Hi,

=If(y>Thresh, x)

makes no sense as what is the VALUE of your 1 column, multi row y range and x range and ThreshX???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Sorry Skip, I was not clear. There are three named ranges:
Thresh is a single cell which contains a threshold value.
X and Y are both 1D ranges containing N cells. They have coincident starting and ending rows.

The expression you quoted "=If(y>Thresh, x)" is the definition used in defining the named range "ThreshX".

In other words, I am creating a named range which is defined as those cells in range X where the equivalent cells in range Y are > thresh.

This bit seems to work in that, if I then type =COUNT(ThreshX) into a cell, the value I get is the number of cells in which Y>thresh. In other words, the size of the range ThreshX does indeed seem to indicate that Excel is acting as if it has a range ThreshX which is a subset of range X.

Is that any clearer?

What is not working is counting the contiguous areas in ThreshX using AREAS().

Tony

 

[Loomah]

i might be wrong but i think it's because what you have effectively done is create a stored formula - not a range. ie you can't select 'threshx'

areas depends on a range as it's argument but you are only passing values

if you were to enter your formula, "=If(y>Thresh, x)", next to your ranges x & y then copy it down to the last row and enter as an array you will get the values from x that meet the criteria from y and a series of 'false's. you could then perform counts, sums, averages etc etc on this new data but you wouldn't be able to identify areas from it.

sorry if this is a bit of a messy theory (which is all it is, a theory) but it's hard to explain in written word in 2 mins!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[N1GHTEYES]

Loomah - yes, I could write the data loading code to populate the cells adjacent to x and y as you suggest. Or I could prepopulate them with enough cells to cover x and y or any size. But both of those would be messier and slower. I was hoping for a nice neat solution where all or most of my processing was done in the background by clever use of dynamic named ranges. Finding an answer to this problem is not the tricky bit. I was mainly trying to understand how Excel does what it does, so that I could more successfully use it optimally for this and future problems.

As to why it does not seem to work, well, it seems almost a philosophical issue...

Does Excel consider ThreshX to be an array of the subset of VALUES of x corresponding to y>thresh? Or is ThreshX the RANGE which consists of the union of the subsets of RANGE x where y>thresh?

Whenever I've read related comments in the help it does not seem to clearly distinguish between these two cases. Certainly you could be forgiven for thinking that the latter was a reasonable interpretation of what it does say.

Putting =COUNT(ThreshX) into a cell does give the correct answer, but maybe that is just because COUNT is happy to accept an array of values as an argument, rather than a range.

As a matter of interest, I entered:

{=AREAS(If(y>thresh,x))} into a cell and also got the VALUE! error.

The above is pretty much equivalent to doing what I was previously proposing using named ranges, so I suppose that is not too surprising.

I wish there was some way of forcing Excel to treat ThreshX as a RANGE as opposed to an array, but maybe there just isn't.

Tony

 

[Loomah]

hi tony
sorry i wasn't clear but i wasn't suggesting that you used the method(s) i suggested but only mentioned them in support of the theory that areas will only work with ranges and not arrays of values.

i feel that there is an answer to the problem of using arreas the way you are trying to - combinations of address and indirect etc etc might work.

or it might just be one of those things like getting median data in a pivot table!!

have you tried other forums for an answer - particularly the ms excel newsgroups?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[N1GHTEYES]

I'd love to, but the stupid security rules here at work mean we have no access to microsoft.public.excel.anything. In fact all newsgroups and a whole bunch of other categories are blocked entirely. Don't get me started on what I think of our security idiots.

To be honest, the question relates to a smallish part of a relatively minor activity in what I'm doing right now, and I can find ways round it anyway. The main reason I posted the question is just that I hate unsolved puzzles. I'll probably investigate further at home if my wife doesn't have any other jobs for me to do. Fat chance...

Tony

 

[Loomah]

samme issues with accessing newsgroups here!
if you do find an answer i'd be interested to hear about it - i'm the same with unsolved puzzles!

good luck

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[N1GHTEYES]

OK, I still don't know the answer wrt making Excel see ThreshX as a range rather than an array, but I do have a relatively neat way of doing what I was after in the first place.

If X and Y are adjacent, equally sized named ranges consisting of contiguous 1D sets of cells; and I want to count the number of peaks in y which are more than the value given by the cell specified by the named range "thresh", I can do so by defining a new range, shiftY, which is identical to y except that it is shifted up by 1 cell, then define ThreshX as those values of the X range where Y>thresh but shiftY is not.

Worked example:

I import some X data from a file.
It goes in sheet1, starting at cell A7 and continuing downwards for say, 100 rows. Similarly, the Y data starts at B7 and continues down for 100 rows.

The named variable DataCount would count the number of cells of data entered and, in this case return 100.

X would be defined as Offset($A$7,0,0,Datacount,1) and Y would be Offset($B$7,0,0,Datacount,1).

Some other cell would hold the threshhold value "thresh".

So I now define shiftY as Offset($A$6,0,0,Datacount,1) NOTE this is defined from A6 not A7.

Finally, ThreshX is defined as =if(Y>thresh,if(shiftY<thresh,x)).

Then putting =Count(ThreshX) in a cell, counts the number of peaks. Which is partly what I was after in the first place.

Tony